Fix “ERROR 1054 (42S22): Unknown column ‘…’ in ‘on clause’” in MySQL

2024-07-17 09:58| 来源: 网络整理| 查看: 265

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.

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章