SQL JOIN: is there a difference between USING, ON or WHERE?

Question

I was wondering if there is any difference in the way SQL performs on these join statements:

SELECT * FROM a,b WHERE a.ID = b.ID  SELECT * FROM a JOIN b ON a.ID = b.ID  SELECT * FROM a JOIN b USING(ID) 

Is there a performance difference? Or algorithmic difference?

Or is it just syntactic sugar?

Answer 1

There is no difference in performance.

However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer.

Examples:

Which is the JOIN, which is the filter?

FROM T1,T2,T3.... WHERE T1.ID = T2.ID AND      T1.foo = 'bar' AND T2.fish = 42 AND      T1.ID = T3.ID  FROM T1     INNER JOIN T2 ON T1.ID = T2.ID    INNER JOIN T3 ON T1.ID = T3.ID WHERE    T1.foo = 'bar' AND T2.fish = 42 

If you have OUTER JOINs (=*, *=) then the 2nd style will work as advertised. The first most likely won't and is also deprecated in SQL Server 2005+

The ANSI-92 style is harder to bollix too. With the older style you can easily end up with a Cartesian product (cross join) if you miss a condition. You'll get a syntax error with ANSI-92.

Edit: Some more clarification

• The reason for not using "join the where" (implicit) is the dodgy results with outer joins.
• If you use explicit OUTER JOINs + implicit INNER JOINs you'll still get dodgy results + you have inconsistency in usage

It isn't just syntax: it's about having a semantically correct query

Edit, Dec 2011

SQL Server logical query processing order is FROM, ON, JOIN, WHERE...

So if you mix "implicit WHERE inner joins" and "explicit FROM outer joins" you most likely won't get expected results because the query is ambiguous...