UNION, INTERSECT and EXCEPT

The three operators

Take two result sets, A = {1, 2, 3} and B = {2, 3, 4}:

A UNION B     → 1, 2, 3, 4   in either, duplicates removed
A UNION ALL B → 1,2,3,2,3,4  everything, duplicates kept
A INTERSECT B → 2, 3         in both
A EXCEPT B    → 1            in A but not B

Two things are worth noting. EXCEPT is not symmetric — B EXCEPT A gives 4, a different answer. And UNION, INTERSECT and EXCEPT all remove duplicates by default, which is a genuine cost: the database must sort or hash the entire result to find them.

Some databases spell EXCEPT as MINUS (Oracle), and the semantics are the same.

The rules every set operator enforces

• Same number of columns in both queries.
• Compatible types, position by position — the first column of one must be comparable with the first column of the other.
• Column names come from the first query. The second query’s aliases are ignored entirely.
• ORDER BY applies to the whole result and may appear only once, at the very end. It cannot be attached to an individual branch.

Note that matching is positional, not by name. If the first query selects (id, name) and the second selects (name, id), and both are text-compatible, the query runs and silently returns nonsense.

Stacking results, not joining them

Joins put tables side by side, matching rows on a condition. Set operators put results one on top of the other, matching them by column position.

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

Three operators cover the whole topic:

• UNION — everything from both, duplicates removed.
• INTERSECT — only rows appearing in both.
• EXCEPT (called MINUS in Oracle) — rows in the first result that are not in the second.

Each has an ALL variant that keeps duplicates: UNION ALL, INTERSECT ALL, EXCEPT ALL.

The rules every set operator enforces:

1. Both queries must return the same number of columns.
2. Corresponding columns must have compatible types.
3. Column names come from the first query; names in the second are ignored.
4. ORDER BY applies to the combined result and goes at the very end, once.

Matching is by position, not by name. SELECT name, email unioned with SELECT email, name compiles happily and produces nonsense — a real bug that type compatibility often fails to catch, since both columns are text.

UNION versus UNION ALL, and why the default is the slow one

UNION removes duplicates, and removing duplicates means sorting or hashing the entire combined result. UNION ALL simply concatenates.

On a million rows, the difference is large — often several times the runtime, plus the memory for the sort. And most of the time the deduplication is unnecessary, because the two queries return inherently distinct rows: this year's orders and last year's orders cannot overlap.

The habit worth forming: write UNION ALL by default, and use UNION only when you specifically need duplicates removed and cannot rule them out. That is the reverse of what most people do, and it is the right way round.

-- combining partitioned tables: overlap is impossible
SELECT * FROM orders_2025
UNION ALL
SELECT * FROM orders_2026;

What INTERSECT and EXCEPT are actually for

Both express set membership questions that would otherwise need subqueries.

-- customers who are also suppliers
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;

-- customers who have never ordered
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

The EXCEPT form is the readable alternative to NOT IN — and unlike NOT IN, it handles NULLs correctly, treating them as comparable to each other. That alone is a reason to prefer it.

Two behavioural details worth knowing. Set operators treat two NULLs as duplicates of each other, which is the opposite of = semantics and consistent with GROUP BY. And EXCEPT removes all copies of a matching row, not one per occurrence, unless you use EXCEPT ALL.

Precedence: INTERSECT binds more tightly than UNION and EXCEPT, so mixed expressions need parentheses to mean what you intend.

Exploration guide

1. Compare UNION with UNION ALL. Switch Operator between them and count the rows. The difference is exactly the duplicates — and UNION paid to find them.Run this
2. Look at INTERSECT. Only rows present in both sides survive. This is the set equivalent of an inner join on every column at once.Run this
3. Reverse EXCEPT. Note which rows survive, then imagine swapping the two queries. The answer changes completely — order matters here and nowhere else among the three.Run this
4. Watch the duplicate handling. With duplicates present in the source, see which operators collapse them and which do not.

UNION ALL is usually the one you want

UNION performs a deduplication pass over the combined result, which means a sort or a hash of every row. When you know the two sets cannot overlap — last month’s orders and this month’s orders, or partitioned tables split by region — that work finds nothing and you have paid for it anyway.

UNION ALL simply concatenates and is frequently several times faster on large result sets. Use UNION only when duplicates are genuinely possible and you want them removed; reach for UNION ALL by default and add the deduplication deliberately.

What trips people up

• Using UNION out of habit. The unnecessary deduplication is one of the more common avoidable costs in reporting queries.
• Mismatched column order. Positional matching means compatible-but-wrong orderings run happily and return garbage.
• Expecting EXCEPT to be symmetric. Swapping the operands gives a different result.
• Putting ORDER BY on a branch. It belongs once, at the end, and applies to the whole combined set.
• Reaching for a set operator when a JOIN is needed. If you want columns from both tables, no set operator will do it — they only ever stack rows.

What to remember

UNION, INTERSECT and EXCEPT combine result sets vertically, requiring the same column count and compatible types matched by position rather than name. All three deduplicate by default, which costs a full sort or hash — so prefer UNION ALL unless you specifically need duplicates removed. EXCEPT is the only one where operand order changes the answer, and none of them is a substitute for a JOIN.

Practical uses

• Combining partitioned or archived tables into one result, which is the classic UNION ALL case.
• Comparing two datasets during a migration. Run A EXCEPT B and B EXCEPT A; if both return nothing, the datasets are identical. This is the fastest reconciliation check there is.
• Adding a total row to a report by unioning the detail with an aggregate.
• Merging results from different sources that happen to share a shape — internal customers and external ones, current staff and alumni.
• Expressing "in this list but not that one" without a correlated subquery.

A worked reconciliation:

-- rows in the old table missing from the new one
SELECT id, amount FROM legacy_payments
EXCEPT
SELECT id, amount FROM payments

UNION ALL

-- rows in the new table that were not in the old one
SELECT id, amount FROM payments
EXCEPT
SELECT id, amount FROM legacy_payments;

An empty result means the migration is clean. Anything returned is a discrepancy with its side identifiable by which half produced it.

Performance notes

• Every deduplicating variant costs a sort or hash. Prefer ALL unless you need otherwise.
• Each branch is executed independently, so index each of them well — a set operator does not make a slow branch faster.
• ORDER BY at the end sorts the combined result, and cannot use an index from either branch.
• With LIMIT after a UNION, the engine generally has to produce both branches fully before it can apply the limit. Pushing the limit into each branch first (each with its own ORDER BY) is often much faster.
• For very large UNION ALL sets over the same shape of data, consider whether the tables should be genuine partitions of one table instead — the planner then handles the combination itself.

Stacking result sets, and the DISTINCT you did not ask for

UNION, INTERSECT and EXCEPT combine whole result sets rather than joining rows. They are simple operators with one expensive default and one strict requirement, both of which are visible immediately.

-- The requirement first: same number of columns, compatible types.
-- The column NAMES come from the first SELECT and the rest are ignored.

SELECT name AS label, 'customer' AS kind FROM customers
UNION ALL
SELECT name,          'product'        FROM products
ORDER BY kind, label;

-- Now the default. UNION removes duplicates; UNION ALL does not.

SELECT 'UNION (distinct)' AS operator, count(*) AS rows_out FROM (
    SELECT city FROM customers UNION SELECT city FROM customers
)
UNION ALL
SELECT 'UNION ALL', count(*) FROM (
    SELECT city FROM customers UNION ALL SELECT city FROM customers
);

-- Deduplicating means sorting or hashing every row, so UNION is the
-- expensive one and it is the one people type by habit. If you know the
-- inputs cannot overlap -- which is usually why you are stacking them --
-- UNION ALL is both faster and more honest about your intent.
--
-- INTERSECT and EXCEPT answer set questions directly.

SELECT 'in both'        AS relation, count(*) AS n FROM (
    SELECT id FROM customers INTERSECT SELECT customer_id FROM orders)
UNION ALL
SELECT 'customers with no orders', count(*) FROM (
    SELECT id FROM customers EXCEPT SELECT customer_id FROM orders)
UNION ALL
SELECT 'orders with no customer',  count(*) FROM (
    SELECT customer_id FROM orders EXCEPT SELECT id FROM customers);

-- That middle row is the anti-join written as a set difference. It finds
-- Dara and Eze, and it reads more directly than the LEFT JOIN ... IS NULL
-- form -- though on a large table the planner usually prefers the join.

Questions people ask

Do the column names have to match? No — only the count and the types. The first query's names are used for the result.

Can I ORDER BY inside one branch? Not usefully; the combined result is what gets ordered. The exception is when a branch has its own LIMIT, where the inner ordering is meaningful — wrap that branch in a subquery.

Is UNION the same as a FULL OUTER JOIN? No. UNION stacks rows vertically; a join widens rows horizontally by matching them.

Does INTERSECT work with different column counts? No — the same rule applies to all set operators.

Which engines support INTERSECT and EXCEPT? PostgreSQL, SQL Server, SQLite and Oracle (as MINUS). MySQL added them in 8.0.31; before that, INNER JOIN and LEFT JOIN ... IS NULL were the workarounds.

How do NULLs behave? Two NULLs count as duplicates for deduplication and as matching rows for INTERSECT and EXCEPT — deliberately unlike =.

Recap in one screen

• Set operators stack results vertically; joins combine them horizontally.
• UNION deduplicates, UNION ALL does not — and ALL should be your default.
• INTERSECT keeps rows in both; EXCEPT keeps rows in the first only, and handles NULLs better than NOT IN.
• Columns match by position, not name; the first query names the result.
• A EXCEPT B plus B EXCEPT A is the quickest way to prove two datasets are identical.