UNION (Transact-SQL)

Combines the result sets of two or more SELECT statements.

Description

The UNION operator in Transact-SQL is used to combine the result sets of two or more SELECT statements and remove duplicate rows from the final result set.

Each SELECT statement within the UNION must have the same number of columns, and the columns must have compatible data types.

The column names in the final result set are typically taken from the first SELECT statement.

Syntax


SELECT expression1, expression2, ... expressionN
FROM table_name
[WHERE conditions]

UNION [ALL]

SELECT expression1, expression2, ... expressionN
FROM table_name
[WHERE conditions]

[ORDER BY column_name [ASC | DESC]]

Parameters

expression1, expression2, ... expressionN: Columns or expressions that are selected from the tables. The number and order of expressions must be the same in all SELECT statements.
table_name: The table from which to retrieve data.
WHERE conditions: Optional. Specifies the criteria for selecting rows.
UNION [ALL]:
- UNION: Returns distinct rows. Duplicate rows are removed.
- UNION ALL: Returns all rows, including duplicates. This is generally faster as it doesn't require sorting to remove duplicates.
ORDER BY column_name [ASC | DESC]: Optional. Sorts the final result set by the specified column. You can only use a column name from the first SELECT statement or by its ordinal position.

Rules and Limitations

The number of columns in each SELECT statement must be the same.
The data types of corresponding columns must be compatible.
The column names in the result set are derived from the first SELECT statement.
UNION performs a distinct sort; UNION ALL does not.
If you use an ORDER BY clause, it must reference a column in the result set, usually by its name from the first query or by its ordinal position (e.g., ORDER BY 1 for the first column).
UNION cannot be used directly with INSERT, UPDATE, DELETE, or CREATE TABLE statements.

Example 1: Basic UNION

Combining Employee and Contractor Names

This example retrieves a list of names from both an Employees table and a Contractors table, removing any duplicate names.


SELECT FirstName, LastName
FROM Employees
WHERE Department = 'Sales'

UNION

SELECT FirstName, LastName
FROM Contractors
WHERE Project = 'Project Alpha'

ORDER BY LastName ASC;

Example 2: UNION ALL

Listing All Contacts, Including Duplicates

This example shows how to use UNION ALL to retrieve all contact names from two different customer lists, keeping any duplicate entries.


SELECT CustomerName, Email
FROM Customers_USA

UNION ALL

SELECT CompanyName, EmailAddress
FROM Customers_Canada;

Example 3: Using Ordinal Position in ORDER BY

Sorting by the Second Column

Demonstrates using the ordinal position (2) to sort the combined results by the last name.


SELECT EmployeeID, LastName, FirstName
FROM Employees
WHERE HireDate < '2020-01-01'

UNION

SELECT ClientID, CompanyName, ContactPerson
FROM Clients
WHERE RegistrationDate < '2020-01-01'

ORDER BY 2 ASC; -- Sorts by the second column (LastName or CompanyName)