If you’re getting error “1054 (42S22): Unknown column ‘…’ in ‘order clause'” when using the UNION clause in MySQL, it could be because you’re trying to reference an aliased column by its column name.

When using the UNION clause in MySQL, if a column has an alias, then you must use that alias in any ORDER BY clause that references that column.

So to fix the error, be sure to reference the column by its alias. Alternatively, you could remove the alias and reference the column name directly.

Example of Error

Here’s an example of code that produces the error:

(SELECT TeacherName t FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY TeacherName ASC;

Result:

ERROR 1054 (42S22): Unknown column 'TeacherName' in 'order clause'

Here I tried to order the results by the TeacherName column, but that column has an alias. The fact that I didn’t use the alias in the ORDER BY clause was enough to produce the error.

Solution 1

One way to fix this issue is to use the alias in the ORDER BY clause:

(SELECT TeacherName t FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY t ASC;

Result:

+--------+
| t      |
+--------+
| Ben    |
| Bill   |
| Cathy  |
| Ein    |
| Faye   |
| Jet    |
| Spike  |
| Warren |
+--------+

That solved the problem.

Solution 2

Another way to do it is to remove the alias altogether:

(SELECT TeacherName FROM Teachers)
UNION
(SELECT StudentName FROM Students)
ORDER BY TeacherName ASC;

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Bill        |
| Cathy       |
| Ein         |
| Faye        |
| Jet         |
| Spike       |
| Warren      |
+-------------+