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?

asked Jul 18 '12 at 07:59 PM in Default

adrianh
20 ● 2 ● 2 ● 4

(comments are locked)

4 answers:

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

answered Jul 18 '12 at 10:58 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

(comments are locked)

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