[thelist] new & improved SQL join syntax?

[Matt Warden]

On 1/19/07, Sarah Adams <mr.sanders at geekjock.ca> wrote:
> "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?

I would say that there is no reason to go back and rework queries.
That doesn't make sense from a cost-benefit perspective.

There are probably a number of reasons that people tend to prefer the
JOIN syntax over the one that doesn't hide the fact that it's a
filtered Cartesian product.

I think the primary reason for this preference is that it *isn't*
always a filtered Cartesian product. OUTER JOINS aren't, and trying to
force the expression of outer joins in the filtered Cartesian product
syntax gets you into things like this:

select *
from foo, bar
where foo.id =* bar.fooid

You say: huh?

Exactly. That's either Sybase or Oracle. Doesn't matter. The point is,
it's not very readable.

So, because you need to use the JOIN syntax for outer joins, I think
it's a good idea to do the same for all joins.

-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.