If you’re running a query in SQL Server, and you get the following error…

Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

…you should check your SQL statement – you’ve probably omitted a column from your SELECT list.

As the error message implies, you’ll probably only see this error if you’re running a query that contains a UNION, INTERSECT or EXCEPT operator.

Simply adding the column to your SELECT list should fix the problem.

Example

Here’s an example of generating the error.

SELECT 
    CatName
FROM Cats
UNION ALL
SELECT 
    DogName 
FROM Dogs
ORDER BY CatId;

Result:

Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The problem here, is that I’m trying to order by the CatId column, but I’m not actually selecting that column in my SELECT list.

The easiest way to fix this is to add that column to my SELECT list.

SELECT 
    CatId AS PetID,
    CatName AS PetName
FROM Cats
UNION ALL
SELECT 
    DogId AS PetID,
    DogName AS PetName
FROM Dogs
ORDER BY PetId;

Result:

+---------+-----------+
| PetID   | PetName   |
|---------+-----------|
| 1       | Brush     |
| 1       | Yelp      |
| 2       | Scarcat   |
| 2       | Woofer    |
| 3       | Flutter   |
+---------+-----------+

Actually, in my case I’m selecting columns with different names (CatId vs DogId, CatName vs DogName), so I decided to use an alias for those columns (PetId and PetName). In this case, I can ORDER BY the alias name (PetId).