If you’re getting “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause”, it’s probably because you’re qualifying a column name with its table name when using an operator such as UNION, INTERSECT, or EXCEPT in MariaDB.

To fix this, either remove the table name or use a column alias.

Example of Error

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

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

Result:

ERROR 1250 (42000): Table 'Teachers' from one of the SELECTs cannot be used in ORDER clause

In this case I tried to order the results by the TeacherName column, but I qualified that column with the table name (I used Teachers.TeacherName to reference the column name).

Referencing tables like this doesn’t work when ordering the results of a UNION operation in MariaDB. The same is true when ordering results of the INTERSECT operator and the EXCEPT operator.

Solution 1

One way to fix this issue is to remove the table name from the ORDER BY clause:

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

Solution 2

Another way to fix it is to use an alias for the column:

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

With this option, we assign an alias to the column, and then reference that alias in the ORDER BY clause.