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 thoughts on “JOIN and comma precedence”

  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.