question

adrianh avatar image
adrianh 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
selectjoin
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

adrianh avatar image
adrianh answered
Thank you. So, this is just a readability thing, no optimisations are done?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yup just readability and best practice - no execution impact at all
0 Likes 0 ·
adrianh avatar image
adrianh answered
Actually, I think I understand now. This is almost like syntactic sugar for a subquery, without the mess. :)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.