UNION, UNION ALL, INTERSECT, and EXCEPT

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Compare two compatible sets of customer IDs.

Run the example

SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (1, 2)
UNION
SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (2, 3)
ORDER BY CustomerID;

Read it step by step

1. Both SELECT branches return one compatible int column.
2. UNION combines the branches and removes the repeated ID 2.
3. UNION ALL would keep both copies; INTERSECT would keep only ID 2; EXCEPT, with the first branch on the left, would keep only ID 1.

Check the result

ORDER BY is applied to the combined result, not to each input branch.

UNION ALL keeps duplicates

SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (1, 2)
UNION ALL
SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (2, 3)
ORDER BY CustomerID;

The shared ID 2 appears twice.

EXCEPT is directional

SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (1, 2)
EXCEPT
SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (2, 3)
ORDER BY CustomerID;

Only ID 1 appears because it exists in the left input but not the right.

Practice

Use INTERSECT to find IDs shared by the sets (1, 2) and (2, 3).

SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (1, 2)
INTERSECT
SELECT CustomerID FROM dbo.Customers WHERE CustomerID IN (2, 3)
ORDER BY CustomerID;