JOIN and comma precedence

Here’s a little something that might trip you up occasionally. Have a look at this test scenario:

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:

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:

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

6 Replies to “JOIN and comma precedence”

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

      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]

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