[thelist] new & improved SQL join syntax?

[Brian Cummiskey]

Sarah Adams wrote:
> I regularly read Rudy Limeback's "Ask the Expert" column
>   
Rudy is to SQL as Eric Meyer is to Css.  I've YET to stump him, and I've 
asked some crazy things in my day.  haha
> <snip />
>
> Has anyone else heard this, and can they shed any light on it? I guess
> I'm just wondering where this change came from, how important it is, do
> I need to go back and re-work countless queries to use this syntax, etc?
>   
Like everything, there's always more than one way to do things.
While the old way works fine, it introduces a little more overhead to 
the server, and it's considered 'depreciated'.

Basically the difference is in the table scan.
see: http://www.everything2.com/index.pl?node_id=1723909

If you have a million queries like this, I probably wouldn't take the 
time to re-write them.  But going forward, I would use the normalized 
syntax that he introduces.  Frankly, if not for performance, it's easier 
to read.
> Also, if anyone has an easy-to-understand tutorial on JOIN clauses
> (especially LEFT JOIN and the like), I'd really appreciate it - it's one
> of those things that I understand but couldn't for the life of me
> explain to someone else :)
>   
Joins are pretty easy.

INNER joins everything from both tables on the match on both sides, and 
often produces multiple rows per record (depending on table data of 
course, often MANY TO MANY)
LEFT (OUTER) JOIN joins only the source row to a matching joined table 
row.  I put outer in parenthesis, as this is also best practice, even 
though LEFT JOIN is an implied outer join. (often, 1 TO MANY or 1 TO 1)
RIGHT is the same thing as left, only backwards.  There's really no use 
for it...  just saves you from switching the order of your ON clause, 
and IMO, should be avoided where possible.


I'm no sql expert by any means, so much of this may be wrong :)