Set operations combine the rows of two queries. UNION concatenates with deduplication, UNION ALL concatenates without it, INTERSECT keeps common rows, and EXCEPT removes the second set’s rows from the first. The choice between UNION and UNION ALL is often the difference between a fast query and a slow one.
Before you start
You should be comfortable with SELECT and matching column lists. This article uses PostgreSQL; the operations are standard.
Step-by-step walkthrough
Step 1: Prefer UNION ALL when duplicates are intended or impossible
UNION sorts or hashes to remove duplicates, which costs time and can hide a real duplicate that indicates a bug. UNION ALL just appends. If the two inputs cannot overlap, or you want every row, use UNION ALL and skip the deduplication entirely.
Step 2: Match column lists and types
Every branch must have the same number of columns with compatible types, and the output takes the names from the first query. A mismatch is a type error or an unintended implicit conversion, so keep the projections aligned and explicit.
Step 3: Use INTERSECT and EXCEPT for set logic
INTERSECT answers “in both”, and EXCEPT answers “in the first but not the second”, which reads more directly than an anti-join in some cases. Both deduplicate like UNION, so add ALL where the engine supports it if you need to preserve duplicates.
Worked scenario
EXCEPT returns subscribers who have not unsubscribed.
SELECT email FROM customers
EXCEPT
SELECT email FROM unsubscribes;Walk through the example
The first set is every customer email and the second is every unsubscribed email. EXCEPT returns the emails present in the first and absent from the second, deduplicated. It is a concise way to express “active subscribers” without writing an explicit NOT EXISTS.
Common mistake
Using UNION everywhere and paying for deduplication, or expecting UNION to preserve input order, which it does not unless you add an outer ORDER BY. Another is combining queries with different column counts, which fails at parse time.
Verify the behavior
Compare UNION and UNION ALL row counts on inputs that overlap and confirm the difference. Assert INTERSECT returns only common rows and EXCEPT returns the first-only rows. Test with one branch returning an extra column and confirm the error.
Interview exercise
When is an anti-join clearer than EXCEPT?
Answer and reasoning
When you need extra columns from the first table or a correlated condition. EXCEPT compares whole rows of the projected columns and deduplicates, so it cannot return additional columns or express a per-row condition. A NOT EXISTS anti-join keeps the full row and lets the subquery correlate, which is why it is the better tool when the projection matters.
Continue learning
Compare alternatives in EXISTS versus join and NULL three-valued logic. Read the PostgreSQL set operations documentation and try the SQL interview questions.