UNION, UNION ALL, INTERSECT, EXCEPT in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this SELECT and Column Expressions, FROM and Table References, Literal Values, Data Types, and Type Casting
What do UNION, INTERSECT and EXCEPT do in SQL?
Set operators combine the results of two separate SELECT statements into one result set.
You're building a list of all active user IDs — some appear in the sessions table, some in the conversions table, and some in both. You could join the two tables, but this is a different kind of question: not "match rows across tables," but "stack the results of two queries together." That's what UNION ALL does. Set operators treat two complete queries as inputs and merge their output, as if both sets of rows had come from a single query.
What is the difference between UNION, UNION ALL, INTERSECT and EXCEPT?
There are four set operators. Each one combines two result sets differently:
UNION ALL stacks both result sets and keeps everything, duplicates included. UNION does the same but removes duplicate rows from the combined result. INTERSECT returns only rows that appear in both result sets. EXCEPT returns rows from the first query that don't appear in the second.
All sessions and all conversions combined:
SELECT user_id FROM sessions
UNION ALL
SELECT user_id FROM conversionsA user in both tables shows up twice. Use UNION to deduplicate:
SELECT user_id FROM sessions
UNION
SELECT user_id FROM conversionsEXCEPT finds rows in the first query with no match in the second — customers who have never placed an order:
SELECT id AS customer_id FROM customers
EXCEPT
SELECT customer_id FROM ordersINTERSECT returns rows that appear in both — users who have sessions and conversions:
SELECT user_id FROM sessions INTERSECT SELECT user_id FROM conversions
You can also attach a label to each row to identify its source:
SELECT user_id, 'session' AS activity_type FROM sessions
UNION ALL
SELECT user_id, 'conversion' AS activity_type FROM conversionsWhat are the column rules for a SQL UNION?
Both queries in a set operation must return the same number of columns, and corresponding columns must have compatible types. Column names in the output come from the first query — aliases in the second query are ignored. When types don't match exactly, use an explicit cast: id::text converts an integer to text so it can stack with a text column.
Does the order of queries matter in EXCEPT?
EXCEPT is directional. The order of the two queries determines the result. A EXCEPT B returns rows in A with no match in B. Swap them and you get rows in B with no match in A — a valid result that answers a different question. A logic error here produces output that looks plausible.
How do you sort the result of a UNION?
ORDER BY applies to the final combined result, not to either individual query. You can't sort one input before the set operation and expect that order to survive. Put ORDER BY after the last query to sort the whole combined result.
Should you use UNION or UNION ALL?
The one thing that trips people up: using UNION when you should use UNION ALL.
UNION deduplicates by sorting or hashing the entire combined result set. On large tables that's a real cost. Default to UNION ALL and switch to UNION only when you specifically need deduplication — for example, when combining two sets that may have overlapping rows and you want each row once.
Practice UNION in SQL
Streamhub's growth team wants to gather every user ID that appears in either session records or conversion records into a single list for batch processing. Duplicates must be preserved — a user with three sessions and two conversions should produce five rows.
Write a query to return the combined list of user IDs.
Assumptions:
- The
sessionstable records each session;user_ididentifies the user. - The
conversionstable records each paid conversion;user_ididentifies the user. - The output should not deduplicate — every record from both tables contributes one row.
Output:
- One row per source record across both tables, with a single column
user_id.
Schema · analytics5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 UNION practice problems
Write a query to return the combined list of user IDs.
Write a query to return each qualifying user ID exactly once.
Write a query to return the customer ID for every customer who does not appear in the orders table.
Write a query to return the user IDs that appear in both the sessions table and the conversions table.
Write a query to return the customer ID for every customer who appears in the orders table.
- 'session' for rows from the sessions table. - 'conversion' for rows from the conversions table.
Write a query to return the user IDs of users who appear in the events table but do not appear in the conversions table.
Write a query to return one row per unique identifier.
Write a query to return any user IDs that have a conversion record but no corresponding session.
Start learning to practice all 9 UNION problems, with instant grading and mastery tracking.
Common questions about UNION
Does UNION remove duplicates inside a single query too?
Yes. UNION deduplicates the whole combined result, so a value repeated twice within the first query still appears once at the end. If you only meant to stack the two results and keep everything, UNION ALL is the one that does that.
Can you combine more than two queries?
Yes. Chain as many as you need with the same operator between each pair. With UNION the deduplication still applies across the whole chain rather than to each pair in turn.
What happens if the two queries have different numbers of columns?
The statement is rejected before it runs, saying each query must have the same number of columns. Types have to line up as well, position by position, which is why an explicit cast is sometimes needed to stack an id against a text column.
Does UNION return rows in a predictable order?
No. Deduplicating may leave the output looking sorted, but nothing guarantees it. Put ORDER BY after the final query when the order matters, and it applies to the whole combined result rather than to either part.
