UNION, INTERSECT and EXCEPT

Stacking results instead of widening them

A join adds columns. A set operation adds rows. When two queries produce the same shape of result and you want them in one list, you stack them:

SELECT id, full_name, 'student' AS kind FROM students
UNION ALL
SELECT id, full_name, 'teacher' AS kind FROM teachers;

The rules are strict and they are what make this predictable. Both sides need the same number of columns, in the same order, with compatible types. Column names come from the first query; the second query’s names are ignored entirely, which surprises people who carefully alias them.

UNION versus UNION ALL, which is not a style choice

UNION ALL concatenates. UNION concatenates and removes duplicate rows, which requires sorting or hashing the entire result.

Two consequences.

The performance one: on a million rows, UNION can be several times slower than UNION ALL for no benefit if the two sides cannot overlap. Students and teachers are different tables; there is nothing to deduplicate. Use UNION ALL unless you specifically want deduplication.

The correctness one, which matters more: UNION silently deletes genuine data. Two payments of ₹500 from the same student on the same day, identical in every selected column, become one payment. Your total is now short by ₹500 and nothing indicates it. If the rows carry an id, include it and the problem disappears; if they do not, UNION is a data-destroying operation dressed as tidiness.

INTERSECT and EXCEPT

INTERSECT returns rows appearing in both results. EXCEPT (called MINUS in Oracle) returns rows in the first that are not in the second. Both deduplicate by default, with ALL variants that do not.

Their best use is comparing two datasets that should be identical:

-- rows the ledger has and the bank does not
SELECT reference, amount FROM ledger
EXCEPT
SELECT reference, amount FROM bank;

-- and the other direction, which is a different question
SELECT reference, amount FROM bank
EXCEPT
SELECT reference, amount FROM ledger;

Running both directions is the point. EXCEPT is not symmetric, and “what did we miss” and “what did they add” are different findings. This pair of queries is the fastest reconciliation tool in SQL, and it also works beautifully for verifying a migration: run the same aggregate against the old and new systems, EXCEPT in both directions, and an empty result on both is a proof rather than an impression.

When to prefer a set operation to a join

EXCEPT and NOT EXISTS can answer the same question:

SELECT id FROM students
EXCEPT
SELECT student_id FROM payments;

SELECT s.id FROM students s
WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);

The EXCEPT version is shorter and reads well when you want a single column. The NOT EXISTS version lets you select other columns from students, which the EXCEPT version cannot without adding them to both sides. Both handle NULLs sensibly, unlike NOT IN. Choose by what you need in the output.

Ordering and parentheses

ORDER BY applies to the whole combined result and belongs at the very end, once:

SELECT ... FROM a
UNION ALL
SELECT ... FROM b
ORDER BY 2 DESC;

An ORDER BY inside one branch is either an error or ignored, depending on the engine. To sort or limit one branch, wrap it in a subquery or a CTE.

Precedence is worth knowing: INTERSECT binds tighter than UNION and EXCEPT, exactly as multiplication binds tighter than addition. Mixing them without brackets is how you get a result nobody predicted. Put the brackets in.

A practical use: the union of similar tables

Data often arrives split by month or by region: sales_2024, sales_2025, or one table per country. A UNION ALL view stitches them into one queryable surface:

CREATE VIEW sales AS
  SELECT *, 2024 AS yr FROM sales_2024
  UNION ALL
  SELECT *, 2025 AS yr FROM sales_2025;

This works, and it is worth knowing that most databases now offer native table partitioning that does the same thing while letting the planner skip irrelevant partitions entirely. The manual union is the portable version and the right first step; if it becomes slow, partitioning is what you are reaching for next.