Hi, I'm not entirely new to SQL, but I've not done a lot with it. And I got to thinking, what is the difference between a "select... inner join... on..." and a "select... several (say 2) tables specified... where..."
SELECT * FROM a, b WHERE a.id=b.id;
SELECT * FROM a INNER JOIN b ON a.id=b.id;
or for 3 tables:
SELECT * FROM a,b,c WHERE a.id1=b.id1 AND b.id2=c.id2;
SELECT * FROM a INNER JOIN b ON a.id1=b.id1 INNER JOIN c ON b.id2=c.id2;
It just looks more verbose. Is there any advantages to using INNER JOIN compared to just a logical statement?
The first way is known as the 'old style join', or otherwise known as ANSI-89 JOIN syntax.
The benefits of doing it the second (proper) way are readability (ok that is subjective, but trust me it's easier - the join conditions are right next to the join operator, not several lines down buried in the where clause) and the fact that if you forget to add a where clause, you've unintentionally done a cross join (Cartesian product), whereas with the proper syntax, you'd get a syntax warning error. If you really wanted a cross join then you'd have to explicity state
Aaron Bertrand has a good overview here http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx
Also, while you can still do the INNER JOIN using the ANSI-89 syntax, you can no longer do OUTER JOIN using the old-fashioned = or = syntax. You must use LEFT or RIGHT JOIN. With that in mind, I would strongly suggest using the standard syntax for clarity.
Actually just gave a presentation at SQL In The City in London around the concept that you must right for clarity as a part of your own and your teams performance.
Please use the modern syntax. You'll be much happier in the long run.
answered Jul 19 '12 at 09:36 AM
Grant Fritchey ♦♦
Actually, I think I understand now. This is almost like syntactic sugar for a subquery, without the mess. :)
answered Jul 19 '12 at 09:54 PM