Session 9 — Set operations: UNION, INTERSECT, EXCEPT

Joins combine tables horizontally; set operations combine vertically. UNION merges two result sets and removes duplicates, UNION ALL keeps every row, INTERSECT keeps rows present in both, and EXCEPT keeps rows in the first but not the second. Both sides must have the same column shape. These four answer combined-list, shared-member, and "first but not second" questions directly.

Plan: Core ideas → worked sets → Quiz.

Time: about 50–60 minutes if you write down both side result sets before combining.

Session 9 — Set operations: UNION, INTERSECT, EXCEPT

The one-paragraph story

So far, every query combined tables horizontally — joins stitch rows side by side using keys. Set operations combine vertically: they stack two result sets on top of each other or subtract them. UNION merges two result sets and removes duplicates; UNION ALL merges and keeps everything; INTERSECT keeps only rows present in both; EXCEPT keeps rows in the first but not the second. The two sides must have the same column shape, because the operation works column by column, row by row. These four operations answer "combined list", "shared members", and "in the first but not the second" questions directly.

---

The shape rule

Both sides must have the same number of columns, and each column pair must be of compatible types. UNION/INTERSECT/EXCEPT compare whole rows, so a three-column result set can only be combined with another three-column result set.

-- OK: both sides are (title) — one text column
SELECT title FROM books WHERE genre = 'fiction'
UNION
SELECT title FROM books WHERE published_year < 2019;

-- Not OK: (title, price) vs (title) — mismatched shapes
SELECT title, price FROM books
UNION
SELECT title FROM books;

When the columns come from different tables with different names, alias them so the result has a coherent header:

SELECT name AS label FROM authors
UNION
SELECT title AS label FROM books;

---

UNION and UNION ALL

SELECT title FROM books WHERE genre = 'fiction'
UNION
SELECT title FROM books WHERE published_year < 2019;

Each side produces a set of titles; UNION returns the combined set with duplicates removed. Side 1: {The Glass Harbour, Static Echoes}. Side 2: {River Clock (2018), North of Noon (2017)}. No overlap here — result has four rows.

SELECT title FROM books WHERE genre = 'fiction'
UNION ALL
SELECT title FROM books WHERE published_year < 2019;

UNION ALL skips the duplicate check and just appends the two result sets. If a title appeared on both sides, it would appear twice. UNION ALL is faster (no duplicate removal), so use it whenever you know there are no duplicates or you actually want them all.

---

INTERSECT

SELECT title FROM books WHERE genre = 'fiction'
INTERSECT
SELECT title FROM books WHERE published_year > 2020;

Rows in both sides: fiction AND published after 2020 → Static Echoes (2021). One row.

INTERSECT ALL keeps multiplicity (how many times a row appears in both), but the plain INTERSECT (one copy per distinct row) covers almost every real need.

---

EXCEPT

SELECT title FROM books
EXCEPT
SELECT title FROM books WHERE genre = 'mystery';

Rows in the first side but not the second: every book title minus the mystery titles. All six minus {River Clock, Prairie Circuit} → The Glass Harbour, Static Echoes, Salt Roads, North of Noon. Four rows.

EXCEPT is the "the ones that aren't in the other list" operation — frequently the cleanest way to express a negative.

---

What they are for

| Question | Operation | |----------|-----------| | "Combine these two lists, no repeats" | UNION | | "Combine, keep every occurrence" | UNION ALL | | "What is in both lists?" | INTERSECT | | "What is in the first but not the second?" | EXCEPT |

These are set-theoretic questions. When you find yourself writing WHERE x NOT IN (…) with NULL headaches, an EXCEPT often expresses the same idea without the NULL trap.

---

Pitfalls to watch for

• Shape mismatch breaks the operation — both sides must agree on column count and types.
• UNION removes duplicates; UNION ALL does not. Choosing ALL when you wanted dedup — or the reverse — changes the row count.
• ORDER BY applies to the whole combined result — write it once, after the last side. An ORDER BY inside one side is only meaningful in exotic cases.
• EXCEPT is direction-sensitive — A EXCEPT B differs from B EXCEPT A.

Session 9 — Worked examples

The bookshop data:

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2)

Example A — UNION

SELECT title FROM books WHERE genre = 'fiction'
UNION
SELECT title FROM books WHERE published_year < 2019
ORDER BY title;

Side 1 (fiction): The Glass Harbour, Static Echoes. Side 2 (before 2019): River Clock (2018), North of Noon (2017). Combined, no duplicates:

| title | |-------| | The Glass Harbour | | North of Noon | | River Clock | | Static Echoes |

(single ORDER BY at the end, sorting the unioned result).

---

Example B — UNION ALL preserves duplicates

Add an overlapping title to make the difference visible:

SELECT title FROM books WHERE genre = 'fiction'
UNION ALL
SELECT title FROM books WHERE published_year > 2020;

Side 1: The Glass Harbour, Static Echoes. Side 2 (after 2020): Static Echoes (2021), Prairie Circuit (2022). Static Echoes appears twice in UNION ALL:

| title | |-------| | The Glass Harbour | | Static Echoes | | Static Echoes | | Prairie Circuit |

With plain UNION, Static Echoes would appear once (4 → 3 rows).

---

Example C — INTERSECT

SELECT title FROM books WHERE price < 20
INTERSECT
SELECT title FROM books WHERE genre = 'fiction';

Side 1 (price < 20): Static Echoes (19.50), Prairie Circuit (18.00). Side 2 (fiction): The Glass Harbour, Static Echoes. Common row: Static Echoes.

| title | |-------| | Static Echoes |

---

Example D — EXCEPT

SELECT title FROM books
EXCEPT
SELECT title FROM books WHERE published_year <= 2019;

First side: all six titles. Second side (2019 or earlier): The Glass Harbour (2019), River Clock (2018), North of Noon (2017). Result: titles after 2019 → Static Echoes (2021), Salt Roads (2020), Prairie Circuit (2022).

| title | |-------| | Static Echoes | | Salt Roads | | Prairie Circuit |

---

Example E — Direction matters

-- Books that are NOT fiction (all minus fiction)
SELECT title FROM books
EXCEPT
SELECT title FROM books WHERE genre = 'fiction';

→ River Clock, Salt Roads, North of Noon, Prairie Circuit. Reversing the operands gives the fiction titles. EXCEPT is not symmetric.

---

Example F — Two tables, one column list

SELECT name AS label FROM authors
UNION ALL
SELECT title AS label FROM books
ORDER BY label;

Labels from two tables stacked vertically: 4 author names + 6 book titles = 10 rows, sorted as one column. The aliases unify the headers; the single ORDER BY sorts the whole stack.