Set operations, and what they cost
UNION removes duplicates and UNION ALL does not — which is not a stylistic difference. One of them has to remember every row it has already emitted.
8 min · runs against the shop dataset
UNION, INTERSECT and EXCEPT combine two result sets that have the same shape. Each has an ALL variant that keeps duplicates, and each bare form removes them — which means remembering every row already emitted, and that is real work rather than a preference.
Every distinct city, once — however many times each appeared.
| citytext |
|---|
| berlin |
| cairo |
| lagos |
| lima |
| osaka |
| oslo |
| perth |
| quito |
8 rows · 48 examined · 2 pages read
Twice as many rows as there are customers. Nothing had to be remembered, so nothing was.
| with_allbigint |
|---|
| 48 |
1 row · 48 examined · 2 pages read
Two nulls are the same row here
Set operations match rows by value, so two nulls are the same row — which = flatly refuses to say. That is the same rule GROUP BY and DISTINCT use, and meeting it a third time is how it stops being surprising.
Two rows in, one row out.
| totalnumeric(10,2) |
|---|
| null |
1 row · 180 examined · 2 pages read
EXCEPT and INTERSECT follow the same pattern. The ALL forms cancel row for row — three of a value on the left against one on the right leaves two — and the bare forms decide by value and emit each surviving value once.