6

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?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
SleeplessKnight
  • 2,125
  • 4
  • 20
  • 28

4 Answers4

11

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
6

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.

Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • You might want to indicate which is 'old style' and which is 'new style'. – Kirk Broadhurst Nov 24 '11 at 01:49
  • does vendor-specific mean it depends on different SQL product(like sqlserver and oracle)? – SleeplessKnight Nov 24 '11 at 01:55
  • 2
    Yes exactly - in fact, there is another question just posted here regarding that very difference: http://stackoverflow.com/questions/8251323/oracle-style-joins-in-sql-server – Jake Feasel Nov 24 '11 at 01:57
  • 'Old style' inner joins, which the question is about, have been in the SQL Standard from SQL-89 to the present Standard (and I predict will remain for evermore). Also the Standard has been ISO (where I = International) since SQL-92 and not just ANSI (where A = American). – onedaywhen Nov 24 '11 at 10:40
1

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.

Elias Hossain
  • 4,410
  • 1
  • 19
  • 33
1

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • Possibly because anyone active in the SO SQL tags for a while will [see](http://stackoverflow.com/q/7442344) [lots](http://stackoverflow.com/q/3056271) [of](http://stackoverflow.com/q/7330433) [examples](http://stackoverflow.com/q/6156757) of inadvertent cartesian joins that would have been avoided with explicit `JOIN` syntax. – Martin Smith Nov 24 '11 at 11:02
  • @MartinSmith: your logic is flawed: I've been active in the SO SQL tags for a while and have not developed an intolerance to this style ;) I rather think it has developed for psychological reasons; I can draw parallels with `SELECT *` and `NATURAL JOIN` along of the lines of, "Bad things might happen if..." – onedaywhen Nov 24 '11 at 11:46