Here’s a little something that might trip you up occasionally. Have a look at this test scenario:
USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; CREATE TABLE a ( a INT ); CREATE TABLE b ( b INT ); CREATE TABLE c ( c INT ); SELECT a.a FROM a LEFT JOIN c ON c.c = a.a; -- Q1 SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a; -- Q2
Q1 and Q2 will produce the same result, right? Wrong! As of MySQL 5.0.12, per the SQL standard, JOIN has had higher precedence than comma ‘,’.
So, you get the following:
mysql> SELECT a.a FROM a LEFT JOIN c ON c.c = a.a; Empty set (0.00 sec) mysql> SELECT a.a FROM a, b LEFT JOIN c ON c.c = a.a; ERROR 1054 (42S22): Unknown column 'a.a' in 'on clause'
This is because, in earlier versions, MySQL interpreted it as ( ( a, b ) LEFT JOIN c ). Now, it interprets this syntax as ( a, ( b LEFT JOIN c ) ). If you run into this problem, the fix is easy. …
[Read more]