Set Operators (UNION, INTERSECT, EXCEPT)

📌 Definition:

📖 Detailed Explanation:

Whereas joins combine tables side-by-side (adding columns), set operators stack result sets vertically (comparing rows), and both SELECTs must have the same number of columns with compatible types. For testers these are powerful reconciliation tools. EXCEPT is especially useful for diffing: 'which expected records are missing from actual' is expected EXCEPT actual, and 'which unexpected records appeared' is actual EXCEPT expected — the two directions together are a full comparison, ideal for validating a migration or an ETL job produced exactly the right rows. INTERSECT confirms overlap (records present in both a source and a target). UNION merges results from similar tables or from two conditions into one list. A key performance/correctness note: UNION deduplicates (an extra sort), so if you know results are already disjoint or duplicates are fine, UNION ALL is faster and preserves counts — which matters when validating row totals.

🔑 Key Points:

• Set operators stack result sets vertically; both SELECTs need matching column shape
• UNION removes duplicates; UNION ALL keeps them (and is faster)
• INTERSECT = rows in both; EXCEPT/MINUS = rows in first but not second
• EXCEPT both directions = a full diff of expected vs actual (migration/ETL validation)
• Use UNION ALL when validating counts so duplicates aren't silently collapsed

🌍 Real-World Example:

📎 Code Example:

-- Migration diff: which source rows are MISSING from the target?
SELECT id, email FROM source_users
EXCEPT
SELECT id, email FROM target_users;

-- And which target rows are UNEXPECTED (not in source)?
SELECT id, email FROM target_users
EXCEPT
SELECT id, email FROM source_users;

-- Customers who bought in BOTH 2025 and 2026
SELECT user_id FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
INTERSECT
SELECT user_id FROM orders WHERE created_at >= '2026-01-01';

-- Merge two test-account sources into one list (keep duplicates for counting)
SELECT email FROM legacy_test_accounts
UNION ALL
SELECT email FROM new_test_accounts;