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

Posted

April 3, 2009

MySQL

Gary

Tags:

comma, join, precedence

Comments

6 responses to “JOIN and comma precedence”

Arjen Lentz

April 3, 2009

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

April 4, 2009

If you use FROM table1 AS tbl, table2 AS coupling and use the tbl and coupling prefixes you’ll be fine.
1. Gary Pendergast
  
  April 4, 2009
  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. Ramon Fincken
    
    August 24, 2009
    
    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]
    1. Ramon Fincken
      
      August 24, 2009
      
      In addition … if you need more examples you know where to find me 🙂
Regina

April 5, 2009

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