SET Operators: Real Use Cases

SET operators aren't just exam topics — data engineers use them daily. Here are the three classic use cases from the course, on IPL data, plus the JOINs & SET cheat sheet.

Use case 1 — Combine information (similar tables)

Combining similar tables means stacking tables that have the same structure but hold different slices of data (one per season, per region, per year) into one table for reporting, using UNION ALL.

Each season's runs are kept in a separate table, runs_2023 and runs_2024 (the playground setup creates them):

SELECT 'IPL 2023' AS season, name, runs FROM runs_2023
UNION ALL
SELECT 'IPL 2024' AS season, name, runs FROM runs_2024;

Why UNION ALL (not UNION)? The season column makes every row unique anyway, so de-duplication would be wasted work — and without it, UNION could wrongly merge two seasons where a player scored the same runs.

Once stacked, you can analyse across seasons:

SELECT name, SUM(runs) AS total_runs
FROM (
    SELECT name, runs FROM runs_2023
    UNION ALL
    SELECT name, runs FROM runs_2024
) AS seasons
GROUP BY name
ORDER BY total_runs DESC;

Use case 2 — Delta detection

Delta detection means finding what changed between two versions of the same dataset (old vs new) — rows that were removed and rows that were added — using EXCEPT in both directions.

squad_old: Virat Kohli, Rohit Sharma, MS Dhoni, David Warner squad_new: Virat Kohli, Rohit Sharma, MS Dhoni, Jos Buttler

-- who left? (in old, not in new)
SELECT name FROM squad_old
EXCEPT
SELECT name FROM squad_new;

-- who joined? (in new, not in old)
SELECT name FROM squad_new
EXCEPT
SELECT name FROM squad_old;

And INTERSECT gives who stayed: Kohli, Rohit, Dhoni. In data pipelines, the same idea finds new or changed rows in today's file compared with yesterday's, so only the delta is loaded.

Use case 3 — Completeness check

A completeness check verifies that no data was lost when moving data from a source system to a target (e.g. a data warehouse): source EXCEPT target must return no rows.

score_source has matches 101, 102, 103, 104. The warehouse table score_dw is missing one (match 103):

SELECT * FROM score_source
EXCEPT
SELECT * FROM score_dw;

Run it both ways: score_dw EXCEPT score_source finds rows in the warehouse that don't exist in the source (wrong or extra data). Because EXCEPT compares whole rows, it also catches a row whose values changed (e.g. runs 201 vs 210), not just missing ones.

Bonus — JOINs and SET cheat sheet

Common mistakes

• ❌ Using UNION to combine seasons and losing rows that happen to be identical. ✅ UNION ALL + a label column.
• ❌ Running the completeness check in one direction only. ✅ Check source→target and target→source.
• ❌ SELECT * EXCEPT SELECT * on tables whose columns are in a different order. ✅ List the columns explicitly in the same order.

Interview tip

Q: How would you verify that a data migration copied every row correctly? A: Compare counts first, then run SELECT cols FROM source EXCEPT SELECT cols FROM target (and the reverse). Both must return zero rows.