Duplicate Handling
Duplicate Handling
Definition
Duplicate handling describes exactly HOW each set operation treats repeated rows — the unifying theme tying together UNION/UNION ALL's contrast (18.1, 18.2) and INTERSECT/EXCEPT's default behavior (18.3, 18.4).
How It Works
| Operation | Duplicate behavior |
|---|---|
UNION | Removes duplicates (set semantics) |
UNION ALL | Keeps all duplicates (bag semantics) |
INTERSECT | Removes duplicates by default |
EXCEPT | Removes duplicates by default |
Only UNION has a widely-used, universally-available "ALL" bag-semantics variant; INTERSECT ALL/EXCEPT ALL (keeping duplicate-aware counts) exist in some dialects (notably PostgreSQL) but are far less commonly needed or supported than UNION ALL.
Edge Cases and Pitfalls
- The DEFAULT for every one of these operations (
UNION,INTERSECT,EXCEPT) is to deduplicate — you have to explicitly opt INTO keeping duplicates (UNION ALL), never opt out of a "keeps duplicates by default" behavior. - Understanding duplicate handling matters for correctness, not just neatness: if you're counting results afterward (
SELECT COUNT(*) FROM (... UNION ...) x), whether duplicates were removed directly changes the count you get. - Since deduplication has a real cost (18.1, 18.7), always ask "do I actually need duplicates removed here" before defaulting to the plain (deduplicating) form of an operation.
Key Takeaways
- UNION/INTERSECT/EXCEPT all deduplicate by default; only UNION ALL is a universally-available way to opt into keeping duplicates.
- Deduplication default vs. opt-in duplicates is uniform: you always explicitly ask to KEEP duplicates, never to remove them beyond the default.
- Duplicate handling directly affects any downstream COUNT or other calculation on the combined result.