x

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
more ▼

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

adrianh gravatar image

adrianh
20 2 2 4

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Jul 18 '12 at 10:58 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 19 '12 at 09:36 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Thank you.

So, this is just a readability thing, no optimisations are done?
more ▼

answered Jul 19 '12 at 05:01 PM

adrianh gravatar image

adrianh
20 2 2 4

yup just readability and best practice - no execution impact at all
Jul 19 '12 at 09:49 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Actually, I think I understand now. This is almost like syntactic sugar for a subquery, without the mess. :)
more ▼

answered Jul 19 '12 at 09:54 PM

adrianh gravatar image

adrianh
20 2 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x103
x20

asked: Jul 18 '12 at 07:59 PM

Seen: 1498 times

Last Updated: Jul 19 '12 at 09:54 PM