CODEWITHSUNDEEP
mysqljoin
Daniel
Daniel

Reputation: 2878

MySQL JOIN behind the scenes

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)

Upvotes: 0

Views: 816

Answers (2)

Mikael Östberg
Mikael Östberg

Reputation: 17166

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.

Upvotes: 1

Vlad Khomich
Vlad Khomich

Reputation: 5880

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

Upvotes: 0

Related Questions