Set operations

Stack results on top of each other instead of joining sideways.

6 min read

Overview

Joins combine tables sideways, attaching columns. Set operations combine two results with the same shape by stacking them into one. No matching happens and no columns attach, because rows simply share a column list and pile up.

june_customers

name
Ana
Ben

july_customers

name
Ben
Cara

Two months of customers hold three different questions. union answers who was active in either month, intersect answers who was active in both, and except answers who was active in June but gone in July. The same two stacks produce three different answers.

How it works

Write two complete queries and put the operator between them. union keeps every distinct row from either side, folding duplicates: Ben appears in both months and lands in the result once. intersect keeps only the rows both sides share. except keeps the left side’s rows minus the right side’s. Toggle the operator and watch the same stacks give three answers.

select name from june_customers
union
select name from july_customers
order by name;

Count the distinct names across the two months before you press play, because that count is exactly what union should return.

Stack, not matchPress play, or step through it yourself.

select name from june_customers
union
select name from july_customers;

june_customers

Ana

Ben

july_customers

Ben

Cara

result

name

same columns, stacked

Each operator has an all form that skips the folding, so union all would keep both Bens. The distinction decides whether you get a list of names or a count that is quietly wrong, and Pitfalls makes it concrete.

Patterns

Set operations are the right tool when the same shape of data lives in more than one place.

1
Stack the periods, tag the source
Monthly extracts, regional tables, this year and last year: union all them into one result and tag each row with a literal column naming its source. Then aggregate the stack as one table.
```
select 'june' as month, name from june_customers
union all
select 'july' as month, name from july_customers;
```
2
This, but not that
When rows were active last month and silent this month, except finds them. It also finds exports present then but missing now. The operator subtracts one result from the other and hands you churn or regressions or gaps.
3
The overlap
Customers retained across both months, users in both cohorts: intersect keeps exactly the rows the two sides agree on. It reads like the retention question it answers.

Trade-offs

Default to union all and promote to union only when you can say what duplicates you are removing and why. The bare union reads friendlier, but its dedupe is both a cost and a decision about your data, and it should be made on purpose.

except and the anti-join both answer "in A but not in B", and they draw the line differently. except compares whole rows and folds duplicates. The anti-join probes by a key you choose and keeps the left rows as they are. Reach for except when the results are the same shape and row-level identity is the question. Reach for the anti-join when a key defines the match, which in real schemas is most of the time.

Pitfalls

UNION folds duplicates you meant to keep

Stack two months of order rows with union and any rows identical across the stack collapse into one. A repeated customer and amount simply vanishes from the total. The sums come out lower and no error is raised, so the query looks tidy while lying. When the stacked rows are facts rather than a membership list, union all is almost always the one you meant.

Columns align by position, not name. The stacks are zipped column by column in order. Same count, compatible types, and the names come from the first query. A swapped column pair fails silently if the types happen to agree.
except is direction-sensitive. June except July is who left, while July except June is who arrived, so read the order out loud before you trust the result.
One order by, at the end. The sort applies to the finished stack rather than to either input, so sorting the pieces separately has no effect on the final order.

Performance

The cost model here is linear because there is no matching step and no row product. union all is the cheapest combination in SQL, a pure concatenation costing what the inputs cost. The folding forms add a real pass. Deciding what is duplicate or shared means comparing rows across the whole stack, usually by sorting or hashing once. On big stacks that comparison dominates the cost and makes union all the natural default.

Practice

EasyCombine two months of active people with union after aligning the projected columns. Each person active in either month should appear exactly once.MediumProduce June-minus-July and July-minus-June result sets with except. Keep operand order explicit, then confirm each exclusive customer appears in the correct direction and shared customers appear in neither.HardStack regional order facts with union all and compare its row count and revenue with union. The union all result must preserve every source row and source total.

Recap

Use set operations when same-shape results should stack. Use a join when related columns must sit side by side.
Use union all to preserve rows and union to deduplicate. Use intersect for overlap and except for left-minus-right.
Check that column counts and types are compatible. Then verify positional alignment and use one final order by for the whole stack.
Prefer an anti-join for keyed “in A without B” questions when a correlated probe states the intent more directly.
Pay for duplicate folding only when the result actually requires it.