Recap — Choosing the Right Set Op

# Choosing the Right Set Op `UNION`, `INTERSECT`, and `EXCEPT` are SQL's set operations. Most analysts use UNION rarely and the other two never. They're often the cleanest answer to questions you'd otherwise write 4 joins to solve. ## UNION vs UNION ALL ```sql SELECT user_id FROM signups_2025 UNION SELECT user_id FROM signups_2026; ``` - `UNION` removes duplicates. Expensive (sorts both sides). - `UNION ALL` keeps duplicates. Cheap. **Use this 90% of the time** — most of the time you don't need dedup, and if you do, you should know you do. ## EXCEPT — set difference "Users who signed up in 2025 but not in 2026" (assuming each year has its own table — silly example, work with it): ```sql SELECT user_id FROM users WHERE signup_year = 2025 EXCEPT SELECT user_id FROM users WHERE signup_year = 2026; ``` The LEFT-JOIN equivalent: ```sql SELECT u25.user_id FROM (SELECT user_id FROM users WHERE signup_year = 2025) u25 LEFT JOIN (SELECT user_id FROM users WHERE signup_year = 2026) u26 ON u25.user_id = u26.user_id WHERE u26.user_id IS NULL; ``` EXCEPT is cleaner. Most engines optimize them similarly. ## INTERSECT — set intersection "Users present in both tables": ```sql SELECT user_id FROM signups_a INTERSECT SELECT user_id FROM signups_b; ``` Equivalent INNER JOIN: ```sql SELECT a.user_id FROM signups_a a INNER JOIN signups_b b ON a.user_id = b.user_id; ``` INTERSECT deduplicates; INNER JOIN doesn't. If `signups_a` has user X twice and `signups_b` has user X three times, INNER JOIN returns 6 rows; INTERSECT returns 1. ## Use cases where EXCEPT/INTERSECT win 1. **Diffing tables for data quality** — "what's in staging but not in prod?": `SELECT id FROM staging EXCEPT SELECT id FROM prod;` 2. **Schema-level dedup** — "Show columns present in table A but missing from table B." (You'd query information_schema.) 3. **Multi-set checks** — "users in A and B but not C": `(SELECT ... FROM A INTERSECT SELECT ... FROM B) EXCEPT SELECT ... FROM C;` ## The "all columns must match" gotcha EXCEPT and INTERSECT compare every column of the SELECT, in order. To compare just `user_id`: ```sql SELECT user_id FROM signups_2025 EXCEPT SELECT user_id FROM signups_2026; ``` Not `SELECT *` — that would compare every column. This trips people up. ## Performance — when to use which - **Small result sets, occasional use** — EXCEPT/INTERSECT are fine. - **Large tables, indexed columns** — LEFT JOIN with NULL filter usually plans better. - **Production diffs over millions of rows** — write it both ways and check EXPLAIN. For interviews, EXCEPT/INTERSECT is the clearer, "I know my SQL" answer. ## Common dedup patterns ```sql -- 1) DISTINCT — column-level dedup SELECT DISTINCT user_id, signup_date FROM users; -- 2) ROW_NUMBER — keep one row per group SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY signup_date DESC) AS rn FROM users ) WHERE rn = 1; -- 3) UNION (without ALL) — dedups across union'd queries SELECT user_id FROM source_a UNION SELECT user_id FROM source_b; ``` ROW_NUMBER is the most powerful — you can keep "the latest", "the first", "the highest paid" by changing the ORDER BY. ## Takeaway UNION ALL by default. EXCEPT and INTERSECT for diffs and intersections — they read more clearly than the JOIN equivalents. For dedup with semantics (keep latest), use ROW_NUMBER, not DISTINCT.