0

I remember reading somewhere / being told / inventing a rumor (^_^) that the two following queries are the same behind the scenes in MySQL servers:

SELECT *
FROM a
JOIN b
ON a.id = b.id

and

SELECT *
FROM a, b
WHERE a.id = b.id

Is it true? If so, is one better than the alternate in other terms? (such as parsing efficiency or standard compliance)

Daniel
  • 2,728
  • 3
  • 21
  • 33
  • possible duplicate of [Performance of inner join compared to cross join](http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join) – soulmerge Apr 20 '11 at 07:19
  • possible duplicate of [SQL left join vs multiple tables on FROM line?](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) – Shakti Singh Apr 20 '11 at 07:20
  • oops... should have spent more time searching :) – Daniel Apr 21 '11 at 07:48

2 Answers2

1

It is in fact true. The first query is according the SQL-89 standard and the second is according to SQL-92.

The SQL-92 standard introduced INNER JOIN .. ON and OUTER JOIN .. ON in order to replace the more complex(?) syntax of SQL-89.

An outer join in SQL-89 would be:

SELECT ...
FROM t1, t2
WHERE t1.id *= t2.id

where in SQL-92 it would be

FROM t1 OUTER JOIN t2 ON t1.id = t2.id

I did prefer SQL-89 over SQL-92 for a long while, but I think SQL Server 2008 compability removed the support for SQL-89 join syntax.

Mikael Östberg
  • 16,982
  • 6
  • 61
  • 79
0

yep, these are identical. But it's not something specific to Mysql - it's just a different joining styles. The one you wrote on top is newer and preffered one

Vlad Khomich
  • 5,820
  • 1
  • 27
  • 39