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”.
Comma joins are a bad habit, they make for unmaintainable queries and don’t easily catch user errors.
If you use FROM table1 AS tbl, table2 AS coupling and use the tbl and coupling prefixes you’ll be fine.
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.
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]
In addition … if you need more examples you know where to find me 🙂
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