UNION and UNION ALL

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• Each SELECT must return the same number of columns, in the same order, with compatible types
• The two SELECTs must read the same table
• The column names must be identical in both SELECTs
• UNION keeps duplicates and UNION ALL removes them

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.

Items to match
• SELECT hometown FROM trainers UNION SELECT city FROM gyms
• SELECT hometown FROM trainers UNION ALL SELECT city FROM gyms
• SELECT type FROM codemon UNION SELECT type FROM gyms
• SELECT name FROM trainers UNION ALL SELECT name FROM gyms UNION ALL SELECT name FROM codemon
Definitions
• 4
• 9
• 6
• 17

Exercise 7 — Different shapes

What happens with SELECT name, hometown FROM trainers UNION SELECT name FROM gyms?

Options
• An error: the two SELECTs do not have the same number of columns
• It works: the gyms get NULL in hometown
• It works and returns 9 rows with one column
• Only the first SELECT runs

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.

Starter code
-- Write your query below
Back to the course