UNION, INTERSECT and EXCEPT

Game: chapter XII

A join puts tables side by side. A set operation puts two results one above the other, and keeps all their rows, the rows they share, or the rows of the first that the second lacks.

UNION: one list from two queries

Write two complete SELECT statements and put UNION between them: the result holds the rows of the first followed by the rows of the second, with duplicates removed. Each half can read its own tables and use its own conditions, which is what makes it useful when one WHERE cannot say both things at once.

The examples use the bakery database from the bakery database page. Say the bakery wants to send a voucher to everyone Alice Turner brought to the shop (the customers whose referred_by is her id, 1), and to everyone who was given a refund. The first half reads one table, the second needs two joins.

SELECT name FROM customer WHERE referred_by = 1
UNION
SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN refund AS r ON r.order_id = o.id
ORDER BY name;

Chloe Martin and Vera Bianchi were brought by Alice and also refunded, so both halves return them, and Chloe had two refunds on the same order, so the second half returns her twice; each still appears once. Alice Turner herself is on the list for a refund of her own. The single ORDER BY at the very end sorts the whole result, not the second half: a set operation takes no ORDER BY or LIMIT inside its halves, only one pair after the last of them.

UNION ALL: keeping every row

UNION ALL stacks the two results as they are. Nothing is compared, so nothing is removed, and it is also the cheaper of the two, since removing duplicates means sorting or hashing every row. Here is the same voucher list with UNION ALL.

SELECT name FROM customer WHERE referred_by = 1
UNION ALL
SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN refund AS r ON r.order_id = o.id
ORDER BY name;

Chloe Martin now comes three times, once from the first half and twice from the second, one row per refund, and Vera Bianchi once from each half. Which one to use depends on the question. For a list of people, UNION. For events you will count or add up afterwards, UNION ALL: two equal amounts on the same day are two payments, and UNION would quietly keep one.

Duplicates are whole rows. Add a column saying why each name is on the list, and the rows Chloe Martin and Vera Bianchi get from the two halves are no longer equal, so even UNION keeps both. Chloe's two refund rows are still equal, and UNION still merges them.

SELECT name, 'referred by Alice' AS reason FROM customer WHERE referred_by = 1
UNION
SELECT c.name, 'had a refund'
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN refund AS r ON r.order_id = o.id
ORDER BY name, reason;

Matching the columns

Both halves must return the same number of columns, and the columns are matched by position, not by name: the first column of one goes under the first column of the other, whatever each is called. The result takes its column names from the first half, which is why only the first half above says AS reason. Matched columns should also hold the same kind of value. This timeline of Oscar Nakamura's dealings with the bakery stacks a date from customer on dates from orders and refund, text on text, with a label to tell them apart. A third half is one more UNION ALL; the final ORDER BY sorts all three together.

SELECT joined AS day, 'joined' AS event FROM customer WHERE id = 20
UNION ALL
SELECT day, 'ordered' FROM orders WHERE customer_id = 20
UNION ALL
SELECT r.day, 'refunded'
FROM refund AS r
JOIN orders AS o ON o.id = r.order_id
WHERE o.customer_id = 20
ORDER BY day, event;

A label column like event is the usual companion of UNION ALL: once the rows are stacked, it is the only way left to know which half each came from. The same stacking is the first step of a recursive CTE, whose anchor and recursive part are joined by UNION ALL.

INTERSECT: the rows both results share

INTERSECT keeps only the rows that appear in both results, once each. Which customers of Hillcrest ordered both on a weekday and at the weekend?

No column says which day of the week a date is, and the functions that compute it differ from one database to the next (PostgreSQL's EXTRACT(DOW FROM ...), SQLite's strftime, which the game's terminal refuses). Here a little arithmetic does it. Every order falls in March 2026, whose Saturdays are the 7th, 14th, 21st and 28th, so the day of the month divided by 7 leaves 0 on a Saturday and 1 on a Sunday: CAST(substr(o.day, 9, 2) AS INTEGER) % 7 IN (0, 1) means "at the weekend" (substr and CAST as on the dates page), and NOT IN (0, 1) means a weekday, Monday to Thursday, since the shop is closed on Fridays. A date bound such as day >= '2026-03-07' is not "the weekend": it is every day from the first Saturday on, weekdays of the three later weeks included.

SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.town = 'Hillcrest'
  AND CAST(substr(o.day, 9, 2) AS INTEGER) % 7 NOT IN (0, 1)
INTERSECT
SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.town = 'Hillcrest'
  AND CAST(substr(o.day, 9, 2) AS INTEGER) % 7 IN (0, 1)
ORDER BY name;

A single query whose WHERE asks for both, % 7 NOT IN (0, 1) AND % 7 IN (0, 1), would return nothing: one order cannot fall both on a weekday and at the weekend. The question is about two different rows of the same customer, and INTERSECT compares the two lists of names instead of a single row with itself. EXISTS or a self-join can ask the same thing.

EXCEPT: the rows of one but not the other

EXCEPT keeps the rows of the first result that the second does not contain. Which customers ordered on a weekday but never at the weekend? This time over every town.

SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE CAST(substr(o.day, 9, 2) AS INTEGER) % 7 NOT IN (0, 1)
EXCEPT
SELECT c.name
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE CAST(substr(o.day, 9, 2) AS INTEGER) % 7 IN (0, 1)
ORDER BY name;

Unlike UNION and INTERSECT, EXCEPT is not symmetrical: swap the halves and the question becomes "at the weekend but never during the week", whose answer, compared by id as the next paragraph explains, is Elena Nakamura, Greta Hughes, Iris Costa and a Zoe Garcia. It is another way of asking about a missing row, alongside NOT EXISTS and NOT IN on the subqueries page, and unlike NOT IN it treats two NULLs as equal, so a NULL in the second result does not empty the answer.

The answer above is one name short. Two different customers are called Zoe Garcia: the one in Hillcrest (id 14) came only on weekdays, the one in Riverside (id 27) only at weekends. The halves compare names, so the Riverside Zoe's weekend orders remove the Hillcrest Zoe from the result. Select c.id, c.name in both halves instead and the two Zoes are different rows again: the answer becomes four, Zoe Garcia (id 14) included. Whenever two people could share a name, compare the ids, and join the names back afterwards if you need them.

Common mistakes

Halves with a different number of columns, which every database refuses. Columns in a different order in each half, which runs and stacks towns under names. A text column matched with a number, which PostgreSQL refuses and SQLite quietly stacks. An ORDER BY at the end of the first half, which is a syntax error: the only one goes after the last half. Using UNION where duplicates are real events to count, or UNION ALL where a list of people was meant. Chaining several different operators without thinking about the order they run in: PostgreSQL works out INTERSECT first, SQLite reads them from left to right, so name each step in a CTE rather than rely on either.

Try it: Not on the weekend menu, on the exercises page.

In the game: Chapter XII, Follow the Money, uses UNION ALL inside WITH RECURSIVE.