SQL - Benefits of JOINs?

Question

Possible Duplicate:
Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?

Hi,

i'ave always retrieved data without joins...

but is there a benefit to one method over the other?

select * from a INNER JOIN b on a.a = b.b;

select a.*,b.*  from a,b where a.a = b.b;

Thanks!

Similar to (if not identical to) this question: http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-mys — ZoFreX, Nov 29 '10 at 16:00

score 8 · Answer 1 · answered Nov 29 '10 at 16:02

The first method using the INNER JOIN keyword is:

ANSI SQL standard
much cleaner and more expressive

Therefore, I always cringe when I see the second option used - it just bloats up your WHERE clause, and you can't really see at one glance how the tables are joined (on what fields).

Should you happen to forget one of the JOIN conditions in a long list of WHERE clause expressions, you suddenly get a messy cartesian product..... can't do that with the INNER JOIN keyword (you must express what field(s) to join on).

Well, there are situations, where old syntax is actually more readable. And usually it's just matter of personal preference. — Pavel Urbančík, Nov 30 '10 at 06:06

score 2 · Answer 2 · answered Nov 29 '10 at 16:01

2

I'd say the biggest benefit is readability. The version with the explicitly named join types are much easier for me to comprehend.

answered Nov 29 '10 at 16:01

Colin Mackay

18,736
7
61
88

score 1 · Answer 3 · answered Nov 29 '10 at 16:02

1

You are using a different syntax for a JOIN, basically. As a matter of best practices, it is best to use the first syntax (explicit JOIN) because it is clearer what the intention of the query is and makes the code easier to maintain.

answered Nov 29 '10 at 16:02

JNK

63,321
15
122
138

score 0 · Answer 4 · answered Nov 29 '10 at 16:01

0

You've always retrieved the data with joins. The second query is using old syntax, but in the background it is still join :)

answered Nov 29 '10 at 16:01

Pavel Urbančík

1,466
9
6

score 0 · Answer 5 · answered Nov 29 '10 at 16:03

This depends on the RDBMS but in the case of SQL server I understand that the utilizing the former syntax allows for better optimization. This is less of a SQL question and more of a vendor specific question.

You can also use the EXPLAIN (SQL Server: Query Execution Plan) type functions to help you understand if there is a difference. Each query is unique and I imagine that the stored statistics can (and will) alter the behavior.

Charles Bretana · Accepted Answer · 2010-11-29T16:27:20.500

These are both joins. they are just two different syntactical representations for joins. The first one, (using the "Join" keyword, is the current ANSI Standard (as of 1992 I think).

In the case of inner joins only, the two differeent representations are functionally identical, but the latter ANSI SQL92 standard syntax is much moire readable, once you get used to it, because each individual join condition is associated with the pair of intermediate resultsets being joined together, In the older representation, the join conditions are all together, along with the overall queries' filter conditions, in the where clause, and it is not as clear which is which. This makes identifying bad join conditions (where for example, an unintended cartesian product will be generated) much more difficult.

But more important, perhaps, is that, when performing an outer Join, in certain scenarios, the older syntax is NOT equivilent, and in fact will generate the WRONG resultset.

You should transition to the newer syntax for all your queries.

SQL - Benefits of JOINs?

6 Answers6