Set Operations and EXISTS

Chapter 13 of the SQL for Beginners series answered questions one row at a time: is this id IN that set, does that EXISTS clause match this customer. Chapter 14 of this 24-chapter series works one level up, treating whole SELECT statements as sets that can be stacked, overlapped and subtracted with UNION, UNION ALL, INTERSECT and EXCEPT — then settles when EXISTS beats the alternatives.

UNION and UNION ALL

A set operation glues two queries together, provided they produce the same number of columns. UNION keeps each distinct row once; UNION ALL keeps every row, duplicates included:

SELECT country FROM customers
UNION
SELECT country FROM customers WHERE id IN (1, 2)
ORDER BY country;

Expected output:

country
Canada
Germany
India
Kenya
Singapore

The second query contributes only India twice, and UNION discards both copies as duplicates of the first. Swap in UNION ALL and the same query returns eight rows, with India printed four times:

SELECT country FROM customers
UNION ALL
SELECT country FROM customers WHERE id IN (1, 2)
ORDER BY country;

Expected output:

country
Canada
Germany
India
India
India
India
Kenya
Singapore

UNION ALL is the cheaper of the two — no de-duplication pass — so use it when you genuinely want the duplicates, as with log lines or appended events. A more natural union mixes different tables that share a shape, such as every name the shop displays:

SELECT first_name || ' ' || last_name AS name FROM customers
UNION ALL
SELECT name FROM products
ORDER BY name;

Expected output: twelve sorted rows, interleaving people and books — Aarav Sharma, Amara Ochieng, Clean Code, Lena Fischer, Marc Tremblay, Matilda, Priya Nair, Python Crash Course, Robotics for Kids, The Silent Patient, The Very Hungry Caterpillar, Wei Ling.

INTERSECT and EXCEPT

Two more operators complete the algebra. INTERSECT keeps only rows that appear in both results; EXCEPT (spelled MINUS in Oracle) keeps rows from the first that are missing from the second:

“Which Indian customer has bought a Kids title?” is a two-set question — intersect the Indian customers with the Kids buyers:

SELECT first_name, last_name FROM customers WHERE country = 'India'
INTERSECT
SELECT c.first_name, c.last_name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Kids';

Expected output:

first_name | last_name
Aarav | Sharma

Priya is Indian but has bought no Kids title, so only Aarav survives. Reverse the operator to get the other half of the story — Indians who have not bought Kids:

SELECT first_name, last_name FROM customers WHERE country = 'India'
EXCEPT
SELECT c.first_name, c.last_name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Kids';

Expected output: Priya | Nair. Same two inputs, one operator swapped, and the two results partition the Indian customers between them.

The rules every set operation follows

Four constraints are worth memorising, because violating any of them fails loudly:

1. Same number of columns. SQLite reports it plainly: SELECTs to the left and right of UNION do not have the same number of result columns.
2. Compatible types. Positions are matched by ordinal, so column 1 is compared with column 1 whatever the first query called it.
3. Column names come from the first query. SELECT first_name AS who ... UNION ALL SELECT name ... labels the result column who, even for the second query’s rows.
4. One ORDER BY at the very end. It sorts the combined result. SQLite rejects a branch-level sort with ORDER BY clause should come after UNION ALL not before, and MySQL asks for parentheses around ordered branches.

Types are paired by position but displayed as the first query declared them, which is a good reason to give both branches the same aliases. Duplicate handling deserves the same suspicion — INTERSECT and EXCEPT both de-duplicate silently, so the counts they return are counts of distinct rows.

Dialect support is nearly universal, with one trap: MySQL gained INTERSECT and EXCEPT only in 8.0.31, so older servers emulate them with JOIN and NOT EXISTS. PostgreSQL and SQLite have supported all four from early on.

EXISTS and NOT EXISTS, revisited

Set operations combine two finished results; EXISTS tests a condition row by row — and NOT EXISTS is the idiomatic way to express “no matching row anywhere”. Finding customers with no orders is a case where it beats NOT IN (see the pitfall below):

SELECT c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Expected output:

first_name | last_name
Marc | Tremblay

The engine walks outer rows and, for each one, probes orders for a match, stopping at the first hit. Because the probe is an indexable equality check, NOT EXISTS scales far better than a set comparison over millions of rows — and it works identically in SQLite, MySQL and PostgreSQL. The left-join spelling (LEFT JOIN orders o ON ... WHERE o.id IS NULL) returns the same single row, but planners tend to handle NOT EXISTS better.

The NULL trap in NOT IN

The nastiest set-operation bug produces no error at all. If the subquery in a NOT IN can return NULL, every comparison becomes unknown and the filter matches nothing. Watch the count collapse to zero:

SELECT COUNT(*) AS never_matches
FROM customers
WHERE id NOT IN (SELECT NULL FROM orders);

Expected output:

never_matches
0

Not “some rows” and not an error — zero, silently, even though six customers have no such id. The null-safe version excludes nulls from the set, and the count becomes the honest 1:

SELECT COUNT(*) AS never_matches
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Expected output: 1 — Marc again. This is why NOT EXISTS is the default recommendation: it has no null edge case, because a missing row is simply a missing row.

Nothing else is affected: IN still matches the values it recognises, and EXISTS never compares values at all — only NOT IN rests on the claim that the value differs from every member, which is exactly what one NULL makes impossible to prove.

Common mistakes

• Reaching for UNION when you meant UNION ALL — de-duplication forces a sort or hash of the entire result; keep UNION only when duplicates are genuinely wrong.
• Sorting inside one branch — SQLite refuses with ORDER BY clause should come after UNION ALL not before; put the single ORDER BY after the last query.
• Mismatched column counts or types — the operation pairs columns positionally, not by name, so SELECT city ... UNION SELECT category, price ... fails immediately.
• NOT IN over a nullable subquery — one NULL and the filter returns nothing, silently; use NOT EXISTS instead.
• Assuming INTERSECT and EXCEPT exist everywhere — MySQL added them in 8.0.31, so check your server before shipping the query.
• Expecting branch-level ordering — only the final ORDER BY survives; a sorted first branch says nothing about the shape of the combined result.

Practice checklist

1. Run the country query with UNION and UNION ALL and account for every row: five versus eight.
2. Union the customer names with the product names (twelve rows), then repeat with INTERSECT to confirm the two lists share nothing.
3. Trigger both documented errors yourself: the column-count mismatch and the misplaced ORDER BY, and read each message aloud.
4. Find customers with no orders three ways — NOT EXISTS, NOT IN and LEFT JOIN ... IS NULL — and check all three print Marc.
5. Break NOT IN deliberately with the null version, then fix it by adding WHERE customer_id IS NOT NULL.

What's next?

Set operations handle one-off combinations, but multi-step reports need names for their intermediate results — and recursion when the data has a shape of its own. Write those named, reusable queries in Common Table Expressions.