JOIN and comma precedence

April 3, 2009 · Posted in MySQL 

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”.

  • Digg
  • del.icio.us
  • Facebook
  • Twitter
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • Slashdot
  • Technorati
  • StumbleUpon

Tags: , ,

Comments

6 Responses to “JOIN and comma precedence”

  1. Arjen Lentz on April 3rd, 2009 6:02 am

    Comma joins are a bad habit, they make for unmaintainable queries and don’t easily catch user errors.

  2. Ramon Fincken on April 4th, 2009 8:22 am

    If you use FROM table1 AS tbl, table2 AS coupling and use the tbl and coupling prefixes you’ll be fine.

  3. Gary Pendergast on April 4th, 2009 12:18 pm

    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.

  4. Regina on April 5th, 2009 6:33 pm

    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

  5. Ramon Fincken on August 24th, 2009 12:11 pm

    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]

  6. Ramon Fincken on August 24th, 2009 12:12 pm

    In addition … if you need more examples you know where to find me :)

Leave a Reply