UNION, UNION ALL, INTERSECT, EXCEPT in SQL
Set operations combine the results of two or more SELECT statements into a single result set. Each operation has distinct rules for which rows survive the combination.
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.
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:
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 conversionsBoth 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.
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.
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.
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.
9 UNION, UNION ALL, INTERSECT, EXCEPT 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.
These problems are part of the UNION, UNION ALL, INTERSECT, EXCEPT lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.