If you’re getting an error in MySQL that reads something like “ERROR 1054 (42S22): Unknown column ‘c.DogName’ in ‘on clause”“, here are three likely causes:
- The column doesn’t exist.
- You’re trying to reference an aliased column by its column name.
- Or it could be the other way around. You could be referencing the column with an alias that was never declared.
If a column has an alias, then you must use that alias when referencing it in any ON
clause when doing a join against two or more tables. Conversely, if you reference a column by an alias, then you need to ensure that the alias is actually declared in the first place.
Example 1
Here’s an example of code that produces the error:
SELECT
c.CatName,
c.CatId,
d.DogId
FROM Cats c
INNER JOIN Dogs d
ON c.DogName = d.DogName;
Result:
ERROR 1054 (42S22): Unknown column 'c.DogName' in 'on clause'
Here I accidentally used c.DogName
in the ON
clause when I meant to use c.CatName
.
In this case, the fix is simple. Use the correct column name:
SELECT
c.CatName,
c.CatId,
d.DogId
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Example 2
Here’s another example of code that produces the error:
SELECT
CatName,
CatId,
DogId
FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Result:
ERROR 1054 (42S22): Unknown column 'c.CatName' in 'on clause'
Here I referenced a non-existent alias in the ON
clause. I used c.CatName
to refer to the CatName
column in the Cats
table. Only problem is that I didn’t declare an alias for the Cats
table. MySQL went looking for a column or alias called c
, couldn’t find one, so spat out the error.
To fix this issue, all we have to do is provide an alias for the Cats
table:
SELECT
CatName,
CatId,
DogId
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Alternatively, we could remove all references to the alias, and just use the full table name:
SELECT
CatName,
CatId,
DogId
FROM Cats
INNER JOIN Dogs
ON CatName = DogName;
Notice that in this example we didn’t prefix the column names in the SELECT
list with the alias. If we had done that, we would have seen the same error, but with a slightly different message:
SELECT
c.CatName,
c.CatId,
d.DogId
FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Result:
ERROR 1054 (42S22): Unknown column 'c.CatName' in 'field list'
In this case, MySQL detected the unknown columns in the SELECT
list before it found the one in the ON
clause. Regardless, the solution is the same – either declare the alias, or remove all references to the alias.
Example 3
Here’s another example of code that produces the error:
SELECT
c.CatName,
c.CatId,
d.DogId
FROM Cats c
INNER JOIN Dogs d
ON Cats.CatName = d.DogName;
Result:
ERROR 1054 (42S22): Unknown column 'Cats.CatName' in 'on clause'
Here, an alias was declared for the Cats
table, but I didn’t use that alias in the ON
clause. MySQL didn’t like that and so it threw an error.
The easiest solution in this case is to use the alias instead of the table name:
SELECT
c.CatName,
c.CatId,
d.DogId
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Of course, another way to deal with it is to remove the alias from the query altogether and just reference the column names.