JOIN and comma precedence

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. You simply need to add brackets around the table list:

mysql> SELECT a.a FROM (a, b) LEFT JOIN c ON c.c = a.a;
Empty set (0.00 sec)

You can read more about this in the MySQL Documentation, from the section starting with “Previously, the comma”.

6 comments

    1. Erm, I’m not following what you mean?

      mysql> SELECT aa.a FROM a AS aa, b AS bb LEFT JOIN c ON c.c = aa.a;
ERROR 1054 (42S22): Unknown column 'aa.a' in 'on clause'

      AS has higher precedence than comma or JOIN, so won’t affect this part of the parsing.

      1. It should be like:

        SELECT tablename.fieldname FROM tablename
        > becomes
        SELECT t.field FROM tablename AS t

        etc…

        Here’s a working example from a project of mine:
        Of cource the DATA_TABLE and MENU_TABLE are defines …

        [code]$sql = ‘SELECT d.title, d.id FROM ‘ . MENU_TABLE . ‘ AS m, ‘ . DATA_TABLE . ‘ AS d WHERE m.active = \’1\’ AND m.data_id = d.id ORDER BY m.pageorder ASC’;[/code]

  3. I agree with Arjen Lentz. , are inherently evil because you never know if a where was left out or if a cross join is intentional. Use CROSS JOIN instead if you want that kind of behavior

Comments are closed.