[thelist] new & improved SQL join syntax?

[jason.handby]

Hi Sarah,

 
> I regularly read Rudy Limeback's "Ask the Expert" column 
> (it's fantastic, BTW, I highly recommend it for anyone who 
> writes SQL on a regular basis). In Wednesday's column he 
> mentioned that the SQL join syntax I've always used (i.e. a 
> comma-separated list of tables, followed by one or more WHERE 
> clauses) is outdated. He's mentioned this previously, but I 
> didn't take it too seriously till I read it again today.
> 
> "Please always try to write your joins using JOIN syntax, not 
> the older style which lists tables using commas."
> http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/
> 0,289625,sid41_gci1239517,00.html
> 
> So he's suggesting this syntax is outdated:
> 
>       SELECT a.foo, a.field1, b.qux, b.field2
>         FROM table1 as a, table2 as b
>        WHERE a.field1 = b.field2
> 
> and should be replaced with:
> 
>       SELECT a.foo, a.field1, b.qux, b.field2
>         FROM table1 as a
>   INNER JOIN table2 as b
>           ON a.field1 = b.field2
> 
> 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?

Yes, you definitely should be using the new syntax. I think it's been
part of the specification for years, but different vendors have brought
it in at different times. Microsoft SQL Server has supported it for
years, so it's all I've ever had to use unless I've been working on
someone else's code.

If I had existing queries using the old syntax which were not broken
then I wouldn't worry too much about fixing them unless someone was
going to pay me to do it! I don't think it will make a performance
difference to queries that are already working. But I think using the
current syntax a good habit to get into because it makes queries more
readable, and because it encourages you to think of other JOIN types you
could be using that have very similar syntax.




Jason