Joins in MySQL 5: #1054 – Unknown column ‘…’ in ‘on clause’

If you used to write MySQL joins for MySQL versions < 5.0 or upgrade your server from MySQL 4 to MySQL >= 5.0 you maybe run into a problem when you execute the following query:

SELECT *
FROM mytable1, mytable2
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

#1054 – Unknown column ‘mytable.mycolumnname’ in ‘on clause’

Even though you made sure that the column exists, the problem persists. It can be a very annoying and time-consuming task to track this kind of error down to it’s cause: MySQL starting from version 5.0 tries to be more compliant to ANSI SQL. The tables are beeing joined in a different order. The solution to this problem is actually very simple. Surround the tables in the FROM clause with round brackets:

SELECT *
FROM (mytable1, mytable2)
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

Even a better solution is to rewrite the query and make use of ANSI joins:

SELECT *
FROM mytable1
INNER JOIN mytable2
ON mytable1.id = mytable2.id
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname;
This entry was posted in Join Hints and tagged , , , , . Bookmark the permalink.

3 Responses to Joins in MySQL 5: #1054 – Unknown column ‘…’ in ‘on clause’

  1. dustin says:

    Good Article ;) exactly what I needed thanks!

  2. Subfader says:

    Thanks! Should trust Google more on first results :)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>