UNION and UNION ALL
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Stacking two results
Where do the trainers live? One query answers that:
SELECT hometown
FROM trainers;And where are the gyms? Another query, another list:
SELECT city
FROM gyms;Now you want one list of every city of the league. A join would put the two tables side by side, but here you have two lists of the same kind of thing, and you want them on top of each other.
That is what UNION does: write the two complete SELECTs, one after the other, with UNION in between:
SELECT hometown AS city FROM trainers
UNION
SELECT city FROM gyms
ORDER BY city;The two lists had 4 + 5 = 9 names, but UNION removes the duplicates: 4 distinct cities remain — Moonlight Hollow included, even though no trainer lives there.
The first SELECT names the columns
The result column is called city because the first SELECT says AS city; the names of the second SELECT are ignored. ORDER BY is written once, at the very end, and sorts the whole stack.
Exercise 2 — UNION ALL keeps everything
Removing duplicates takes work, and sometimes you want them. UNION ALL keeps every row:
SELECT hometown AS city FROM trainers
UNION ALL
SELECT city FROM gyms
ORDER BY city;9 rows: Azure City appears four times (two trainers, two gyms). Use UNION ALL when the two lists cannot overlap or when repeats carry meaning, and UNION when you want a clean set of distinct values.
UNION ALL by default
When you know there are no duplicates (or do not care), prefer UNION ALL: it skips the deduplication step and is faster on big tables.
Exercise 3 — The shape rule
Which statement about UNION is true?
Hint : Columns are matched by position, not by name.
Exercise 4 — Every city, once
Complete the query so that it lists every city of the league — trainers' hometowns and gyms' cities — each once.
Hint : Each once means no duplicates, and the second SELECT must provide a city.
Exercise 5 — Tagging where rows come from
Once stacked, nothing tells you which SELECT a row came from. Add a constant column as a label:
SELECT name, 'trainer' AS kind FROM trainers
UNION ALL
SELECT name, 'gym' FROM gyms
ORDER BY kind, name;'trainer' and 'gym' are plain text values repeated on every row of their SELECT. Each SELECT is complete and independent: it can have its own WHERE, INNER JOIN or GROUP BY. Fire gyms and fire Codémons together:
SELECT name, 'gym' AS kind FROM gyms WHERE type = 'fire'
UNION ALL
SELECT name, 'codemon' FROM codemon WHERE type = 'fire'
ORDER BY kind, name;Shared at the end only
ORDER BY and LIMIT are the only clauses shared by the whole stack, and they go after the last SELECT. SQLite does not accept parentheses around each SELECT: just write them one after the other.
Exercise 6 — How many rows?
4 trainers (3 distinct hometowns), 5 gyms (4 distinct cities, 3 of them also hometowns), 8 Codémons of 6 types (every gym type is a Codémon type). Match each query with its number of rows.
SELECT hometown FROM trainers UNION SELECT city FROM gymsSELECT hometown FROM trainers UNION ALL SELECT city FROM gymsSELECT type FROM codemon UNION SELECT type FROM gymsSELECT name FROM trainers UNION ALL SELECT name FROM gyms UNION ALL SELECT name FROM codemon49617Exercise 7 — Different shapes
What happens with SELECT name, hometown FROM trainers UNION SELECT name FROM gyms?
Hint : Stacking needs the same shape on both sides.
Exercise 8 — Water, labelled
Complete the query so that it lists the water Codémons and the water gyms together, each row tagged 'codemon' or 'gym' in a kind column.
Hint : The label is a text constant; so is the type you filter on.
Exercise 9 — The fighters' list
Professor Pi wants the list of every trainer who took part in at least one battle — as fighter 1 or fighter 2.
Return each name once, sorted by name. Write two joins between trainers and battles — one through trainer1_id, one through trainer2_id — and stack them with UNION.
-- Write your query below