asked
inner join vs a select with a bunch of tables specified and a where clause
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..." E.g. 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? A
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 `CROSS JOIN` 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.
Thank you. So, this is just a readability thing, no optimisations are done?
yup just readability and best practice - no execution impact at all
Actually, I think I understand now. This is almost like syntactic sugar for a subquery, without the mess. :)
9 People are following this question.
