UNION, INTERSECT and EXCEPT
Set operators combine the results of two queries into one. This lesson covers UNION, UNION ALL, INTERSECT and EXCEPT, the rules both queries must follow, and why UNION ALL is usually the one you want.
- UNION ALLEverything from both queries
- UNIONEverything, duplicates removed
- INTERSECTOnly rows in both
- EXCEPTRows in the first but not the second
Both queries need matching columns and types.
Stacking results
A JOIN puts columns side by side. A set operator stacks rows from two queries on top of each other, or keeps only the rows they share or do not share.
Both queries must return the same number of columns, in the same order, with compatible types. Column names come from the first query, and ORDER BY goes once at the very end.
The four operators
- UNION ALL: every row from both queries, duplicates included. Fastest, because nothing is compared.
- UNION: rows from both queries with duplicates removed, which needs a sort or hash step.
- INTERSECT: only rows that appear in both queries.
- EXCEPT: rows from the first query that do not appear in the second.
Example: one contact list from two tables
SELECT Email, N'Customer' AS Source FROM Customers
UNION ALL
SELECT Email, N'Subscriber' FROM NewsletterSubscribers
ORDER BY Email;
-- Subscribers who have never bought anything
SELECT Email FROM NewsletterSubscribers
EXCEPT
SELECT Email FROM Customers;
-- People who are both
SELECT Email FROM NewsletterSubscribers
INTERSECT
SELECT Email FROM Customers;Things to watch
- Use UNION only when duplicates really must go; otherwise UNION ALL avoids an expensive sort on large results.
- UNION, INTERSECT and EXCEPT treat two NULLs as equal, unlike = in a WHERE clause.
- INTERSECT is evaluated before UNION and EXCEPT; add brackets when you mix them.
- In LINQ, Concat becomes UNION ALL, Union becomes UNION, Intersect becomes INTERSECT and Except becomes EXCEPT.
Interview question
What is the difference between UNION and UNION ALL?
UNION ALL returns every row from both queries, including duplicates, and does no extra work. UNION removes duplicate rows, which forces SQL Server to sort or hash the combined result. Use UNION ALL unless you need duplicates removed; on large results it is noticeably faster.
Want the full details? Read the official Microsoft documentation for this topic.