Set Operations

Set operators stack result sets vertically, where joins combine them horizontally. There are four, and the interview question is almost always the same one: UNION versus UNION ALL.

The four operators

SELECT city FROM customers
UNION            -- all rows from both, DUPLICATES REMOVED
SELECT city FROM suppliers;

SELECT city FROM customers
UNION ALL        -- all rows from both, duplicates kept
SELECT city FROM suppliers;

SELECT city FROM customers
INTERSECT        -- only rows present in both
SELECT city FROM suppliers;

SELECT city FROM customers
EXCEPT           -- in the first, not in the second (MINUS in Oracle)
SELECT city FROM suppliers;

customers: London, Paris, Paris, Tokyo
suppliers: Paris, Berlin

UNION      → London, Paris, Tokyo, Berlin
UNION ALL  → London, Paris, Paris, Tokyo, Paris, Berlin
INTERSECT  → Paris
EXCEPT     → London, Tokyo

UNION versus UNION ALL

**UNION deduplicates, and deduplication costs a sort or a hash of the entire combined

result.** UNION ALL just concatenates. On large inputs that is a substantial difference, and it is the single most-asked question about set operators.

The rule: use `UNION ALL` unless you specifically need duplicates removed. When the inputs are disjoint by construction — this year's orders and last year's orders, active and archived rows — deduplication cannot remove anything, so UNION is pure wasted work.

-- Disjoint by construction: UNION ALL is correct and cheaper
SELECT id, total FROM orders_2024
UNION ALL
SELECT id, total FROM orders_2023;

It also changes results, not just speed: unpivoting or building a report where two branches legitimately produce identical rows will silently lose rows under UNION.

The compatibility rules

Every branch must agree on:

1. The same number of columns.
2. Compatible types, positionally. Column 3 of each branch is matched by position, not

by name — mismatched orders produce a type error, or worse, a query that runs with values in the wrong columns.

1. Column names come from the first branch. Aliases in later branches are ignored.

SELECT first_name AS name, 'employee' AS source FROM employees
UNION ALL
SELECT company_name, 'supplier' FROM suppliers;     -- names come from branch 1

That literal 'employee' / 'supplier' column is a common idiom: it tags each row with where it came from, which is usually needed the moment two sources are combined.

ORDER BY and LIMIT

They apply to the whole result, and go once, at the end:

SELECT name, salary FROM employees_us
UNION ALL
SELECT name, salary FROM employees_eu
ORDER BY salary DESC
LIMIT 10;

To limit a single branch, wrap it — a bare LIMIT before UNION is a syntax error in most engines:

(SELECT name FROM employees_us ORDER BY salary DESC LIMIT 5)
UNION ALL
(SELECT name FROM employees_eu ORDER BY salary DESC LIMIT 5);

MySQL has no INTERSECT or EXCEPT

Before MySQL 8.0.31 neither exists, so know the rewrites — they are a fair interview question in their own right:

-- INTERSECT via INNER JOIN (or IN)
SELECT DISTINCT c.city FROM customers c
JOIN suppliers s ON s.city = c.city;

-- EXCEPT via LEFT JOIN … IS NULL (the anti-join)
SELECT DISTINCT c.city FROM customers c
LEFT JOIN suppliers s ON s.city = c.city
WHERE s.city IS NULL;

-- EXCEPT via NOT EXISTS — NULL-safe, usually the better answer
SELECT DISTINCT c.city FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM suppliers s WHERE s.city = c.city);

Note that INTERSECT and EXCEPT both deduplicate by default, which is why the rewrites need DISTINCT to match. INTERSECT ALL and EXCEPT ALL keep multiplicities where supported.

Set operators and NULL

Unlike =, set operators treat NULLs as equal to each other for deduplication and matching. Two rows that are NULL in the same column are duplicates under UNION, and INTERSECT will match them. That inconsistency with the rest of SQL's three-valued logic surprises people and is worth knowing.

Mistakes that cost the interview

• `UNION` where `UNION ALL` was meant — slower, and it silently drops legitimate

duplicate rows.

• Mismatched column counts or order. Branches match by position.
• Aliasing in the wrong branch. Only the first branch's names survive.
• `ORDER BY` in the middle. It belongs at the end, or inside parentheses.
• Assuming `INTERSECT`/`EXCEPT` exist on an older MySQL.

Practise these on the SQL sheet.