Set Operations: UNION, INTERSECT, EXCEPT

Every query you have written returns one result table. Sometimes a report needs two lists in one: the shop’s “needs attention” page should show products that are out of stock and products that nobody has ever ordered, stacked into a single result. Joins cannot do that. A join glues tables side by side, adding columns. Set operations stack query results on top of each other, adding rows. This closing lesson of the module gives you all three: UNION, INTERSECT, and EXCEPT, plus the payoff of a promise from Module 3.

UNION and UNION ALL: Stacking Two Results

Start with the two lists themselves. Out-of-stock products is a Module 1 query: WHERE stock = 0 finds the USB-C Hub and the Wool Beanie. Never-ordered products is the LEFT JOIN pattern from lesson 3.2: only the Wool Beanie has no order_items row. Notice the overlap: the Wool Beanie is on both lists. That overlap is exactly what makes the next choice matter.

UNION stacks the second query’s rows under the first query’s rows, then removes duplicate rows. Predict what that means here:

SELECT name FROM products WHERE stock = 0
UNION
SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

That silent deduplication surprises almost everyone. If you want to keep every row, duplicates included, write UNION ALL. Click between the two:

SELECT name FROM products WHERE stock = 0
UNION
SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.id IS NULL
ORDER BY name;

name
USB-C Hub
Wool Beanie

SELECT name FROM products WHERE stock = 0
UNION ALL
SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.id IS NULL
ORDER BY name;

name
USB-C Hub
Wool Beanie
Wool Beanie

Treat UNION ALL as your default and upgrade to UNION only when you actually need the dedupe. You have already been using UNION ALL this way: the recursive CTEs of lesson 5.6 use it precisely because every generated row should be kept.

One Labeled List, and the Rules

A raw list of names does not tell the shop manager why each product needs attention. The fix is a label column: each branch selects a constant string naming its source. This is the everyday, practical use of UNION ALL, one combined report from two different questions.

Before you run it, look at the ORDER BY on the last line. Which of the two SELECTs does it belong to?

SELECT name, 'out of stock' AS source
FROM products
WHERE stock = 0
UNION ALL
SELECT p.name, 'never ordered' AS source
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.id IS NULL
ORDER BY name, source;

name | source
USB-C Hub | out of stock
Wool Beanie | never ordered
Wool Beanie | out of stock

Neither. The ORDER BY belongs to the combined result: it always goes after the last branch and sorts the whole stack, which is why the two Wool Beanie rows sit together. You cannot give each branch its own ORDER BY; sorting one half of a stack would be meaningless, and SQLite rejects the syntax. The same goes for LIMIT: last, applied to the whole result.

Notice something else: the Wool Beanie appears twice here, and it would still appear twice if you swapped UNION ALL for UNION. Deduplication compares entire rows, and ('Wool Beanie', 'out of stock') is not the same row as ('Wool Beanie', 'never ordered'). Adding a label column quietly disables the dedupe you might have been counting on.

Two more rules hold for every set operation. First, each branch must return the same number of columns, and the values stacked into each column should be the same kind of thing (engines like PostgreSQL enforce type compatibility strictly). Second, the result’s column names come from the first SELECT, which is why the alias AS source on the first branch names the column for the whole result. What happens when the two branches disagree on how many columns they return?

SELECT name, price FROM products WHERE stock = 0
UNION
SELECT name FROM products WHERE price > 200;

INTERSECT and EXCEPT

UNION answers “in either list”. Its two siblings answer the other set questions: INTERSECT keeps only rows that appear in both results, and EXCEPT keeps rows from the first result that do not appear in the second. Both deduplicate, like UNION. Order matters for EXCEPT: A EXCEPT B and B EXCEPT A are different questions.

SELECT name FROM products WHERE stock = 0
INTERSECT
SELECT p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

name
Wool Beanie

SELECT c.country
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'pending'
INTERSECT
SELECT c.country
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'delivered';

country
Ireland

SELECT name FROM products
EXCEPT
SELECT p.name
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.id;

name
Wool Beanie

The first chip finds the product that is both out of stock and never ordered: the overlap UNION was deduplicating, made visible. The second asks which countries have both a pending and a delivered order, and only Ireland qualifies. The third is an old friend: all products EXCEPT the ordered ones leaves the Wool Beanie. You now have three ways to find it: the LEFT JOIN with IS NULL from 3.2, NOT EXISTS from 5.3, and EXCEPT. All correct; the join and NOT EXISTS forms let you select extra columns from the outer row, while EXCEPT compares whole rows only.

Paying Off the Module 3 Promise: FULL JOIN in MySQL

In lesson 3.6 you learned that MySQL has no FULL JOIN, and that MySQL users emulate it “with UNION, an operator you will meet in Module 5”. You have now met it, so here is the shape. This one is for reading, not running (our SQLite has a real FULL JOIN, and this is MySQL syntax):

-- MySQL: emulate FULL JOIN with LEFT + RIGHT + UNION
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
UNION
SELECT c.name, o.id AS order_id
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id;

The LEFT JOIN contributes every matched pair plus the unmatched left-side rows. The RIGHT JOIN contributes every matched pair plus the unmatched right-side rows. Stack them and the matched pairs are in there twice, which is exactly the problem UNION’s deduplication solves: one copy of each matched pair survives, and the unmatched rows from both sides come along. The result is a FULL JOIN built from parts you already own.

Practice: Countries Still Waiting

The shop manager wants to know which markets have never had a completed sale.

SELECT country FROM customers
EXCEPT
SELECT c.country
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'delivered'
ORDER BY country;