What is the difference between the two SQL join notation?

Question

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 they return are the same. Are there any differences between the two? For example, how does the DBMS work or in terms of query?

Answer 1

There is no difference in operations between the two queries.

However, an explicit join record is the best style to learn and use; keep the other (old) old code.

Answer 2

One is the old style, and the new is the new (ANSI). The main reason I found why you want to use the new style is the standard support for external connections. In the old style, external connections are vendor-specific. The new style has a standard:

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

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

Answer 3

Basically, there is no difference between the two queries in the job.

However, both have the same execution plan and have the same cost, which means that both requests take equal time to execute (same performance).

Using the join operator is a modern way.

Answer 4

The two are semantically equivalent (among other theme variations). One of the differences is that many users on Stackoverflow are very vocal about intolerant of old-style inner joins (your SQL 2), until one of them risks being omitted in addition to being warned in comments. You will probably also notice that the term “anti-pattern” is used, which is nonsense. I have not experienced such style intolerance outside the SO community. In fact, "old-style" inner joins are very common in the SQL literature.