SQL - Benefits of JOINs? [duplicate]

This question already has answers here: Closed 12 years ago.

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 开发者_JS百科a.*,b.*  from a,b where a.a = b.b;

Thanks!

---

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).

---

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

---

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.

---

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.

---

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

---

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.