What's the difference between the two SQL join

问题:

SQL 1: select * from t1 join t2 on t1.f1 = t2.f2

SQL 2: select * from t1,t2 where t1.f1 = t2.f2

The results that they return are same. Are there any differences between them? For example, in how the DBMS runs them, or in the query plan?

回答1:

There is no operational difference between the two queries.

However, the explicit join notation is the better style to learn and use; leave the other for (as yet unchanged) legacy code.

回答2:

One is old style and one is new (ANSI) style. The main reason that I've found why you would want to use the new style is for standard support of outer joins. With the old style, outer joins are vendor-specific. New style has it standard:

select * from t1 left outer join t2 on t1.f1 = t2.f2

In your example, SQL 1 is the new and SQL 2 is the old style, btw.

回答3:

Basically, there are no difference between the two queries in operation.

However, both have same execution plan and have same cost that mean both query take equal time to execute(same performance).

Use of join operator is a modern way.

回答4:

The two are semantically equivalent (among other variations on the theme). One difference is that many users on Stackoverflow are very vocal in expressing their intolerant to 'old style' inner joins (your SQL 2), to the point where anyone posting them risks being downvoted in addition to being admonished in comments. You're also likely to see the term 'anti-pattern' applied, which is nonsense. I've not encountered this style intolerance outside of the SO community. In fact, 'old style' inner joins are very common in the SQL literature.