If you’re getting an error that reads “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in global ORDER clause” when using the UNION clause in a MySQL query, it’s probably because you’re qualifying a column name with its table name.

This doesn’t work in MySQL.

To fix this issue, 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 global ORDER clause

Here 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).

MySQL doesn’t allow tables to be referenced in this way when ordering the results of a UNION operation.

Solution 1

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

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

Result:

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

Solution 2

If the previous option is not suitable, another way to fix the issue is to use an alias. In other words, assign an alias to the column, then reference that 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 |
+--------+