Set Operation Performance

Definition

Set operation performance covers the practical cost considerations of UNION/INTERSECT/EXCEPT — chiefly, the real cost of deduplication (18.6), and how set operations compare to equivalent joins or subqueries for the same task.

How It Works

Deduplicating requires comparing rows against each other — conceptually similar to a sort-and-scan or hash-based grouping operation over the FULL combined result, an inherently more expensive step than simply concatenating rows (UNION ALL). For large result sets, this comparison cost is genuinely significant, not just theoretical.

Practical guidance:

1. Use UNION ALL instead of UNION whenever duplicates are impossible or don't matter (18.6) — the single most impactful, easy optimization in this area.
2. For INTERSECT/EXCEPT-style questions, compare against an equivalent EXISTS/NOT EXISTS or JOIN formulation (7.12, 17.12) — modern query optimizers are often very good at rewriting these into efficient plans, but which formulation is fastest can genuinely vary by engine and by the specific data/indexes involved.
3. Indexes on the columns involved in the set operation's comparison can significantly speed up the deduplication/matching step, exactly as they would for an equivalent JOIN or GROUP BY.

Edge Cases and Pitfalls

• There's no universal "always faster" answer between a set operation and an equivalent join/subquery formulation — the right choice can depend on data size, existing indexes, and the specific engine's optimizer; when performance genuinely matters, comparing actual execution plans (Chapter 35) for the realistic alternatives is more reliable than assuming based on general rules of thumb.
• A common, low-effort performance win that's easy to overlook: check whether a query actually NEEDS a plain (deduplicating) UNION before assuming it does — many real queries reach for UNION out of habit when UNION ALL would give an identical, faster result.
• Combining several large UNIONs (not UNION ALL) in a single big query can be a genuine hidden cost source in reporting/analytics queries — worth auditing specifically when such a query is slower than expected.

Key Takeaways

• Deduplication in UNION/INTERSECT/EXCEPT has a real computational cost, roughly comparable to a sort/hash-based grouping pass over the combined data.
• Defaulting to UNION ALL whenever duplicates aren't a concern is the single easiest performance win in this area.
• No universal answer exists for "set operation vs. equivalent join/subquery" — compare actual execution plans when performance genuinely matters (Chapter 35).