Recap — 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

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):

SELECT user_id FROM users WHERE signup_year = 2025
EXCEPT
SELECT user_id FROM users WHERE signup_year = 2026;

The LEFT-JOIN equivalent:

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":

SELECT user_id FROM signups_a
INTERSECT
SELECT user_id FROM signups_b;

Equivalent INNER JOIN:

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:

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

-- 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.