If you’re getting error “ERROR 1054 (42S22): Unknown column ‘colname’ in ‘order clause’” in MariaDB, it may be that you’re trying to reference an aliased column by its column name.

This is a common error when running queries that join two or more tables. It can also happen when using operators such as UNION, INTERSECT, and EXCEPT.

Generally, if a column has an alias, then you must use that alias in any ORDER BY clause that references that column.

To fix the error, simply reference the column by its alias.

Alternatively, you could remove the alias altogether and reference the column name directly.

Example of Error

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

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

Result:

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

Here I didn’t use the alias in the ORDER BY clause which resulted in 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)
EXCEPT
(SELECT StudentName FROM Students)
ORDER BY t ASC;

Result:

+-------+
| t     |
+-------+
| Ben   |
| Cathy |
+-------+

In this case, the alias becomes the column header in the result.

Solution 2

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

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

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

This time the column name becomes the column header.