MySQL 5.0.12: join processing changes

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.

First example

Table1
Userid
Name (V1)

Table2
Postid
Name (V2)

SELECT * FROM Table1 NATURAL JOIN Table2;
SELECT * FROM Table1 JOIN Table2 USING (Name);

Result before 5.0.12
UserId Name Postid Name

Result from 5.0.12
UserId Name Postid (when V1 is not null then V1 else V2)

Second example

FROM table1 AS a, table2 AS b INNER JOIN table3 AS c ON a.field1 =
c.field2

The above fails, because the b JOIN c is evaluated before the a alias has
been created. The solution is to use parentheses to force an order of evaluation:

FROM (table1 AS a, table2 AS b) INNER JOIN table3 AS c ON a.field1 =
c.field2

For more information please read the MySQL manual.


Leave a Reply