SQL Server Error Msg 104 – ORDER BY items must appear in the select list if the statement contains a UNION

SQL Server Error Message

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

Reason for the Error

This error usually happens when you use UNION operator in SQL Server and combine the results of the multiple SELECT statements and you specify an ORDER BY clause where the columns are not included in the SELECT list.

Example

Assume that you have two tables that contains Students and PartTimeStudents in a College. Both tables have the fields – First Name and LastName and you wish to contacenate both the FirstName and LastName and display the Full Name. You have used LastName in the ORDER BY clause as shown below.

SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[Students]
UNION
SELECT [FirstName] + [LastName] AS [FullName]
FROM [dbo].[PartTimeStudents]
ORDER BY [LastName]

Since LastName is used in the ORDER BY clause and not used in the SELECT Query when using UNION, you’ll notice the below error.

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

Solution

This error message can be avoided by simply removing the ORDER BY clause from your query. If you need the ORDER BY LastName , you’ll need to include the field (LastName) in the SELECT query.

SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[Students]
UNION
SELECT [FirstName] + [LastName] AS [FullName], [LastName]
FROM [dbo].[PartTimeStudents]
ORDER BY [LastName]