Level 13: Combining results: UNION, INTERSECT, EXCEPT

Lesson

So far, a query returned a single result. Set operators combine the results of two queries, the way you combine two lists. You write a complete SELECT, the operator, then a second complete SELECT.

Condition: both SELECT must return the same number of columns, of the same kind, in the same order. SQL matches the columns by position, not by name: the first with the first, the second with the second. The result takes the column names of the first SELECT.

UNION stacks both results and removes duplicates: a person who belongs to both clubs appears only once. UNION ALL stacks everything, duplicates included; it is faster, since it does not have to look for duplicates. Use UNION ALL when duplicates are impossible or wanted.

INTERSECT only keeps the rows present in both results: the members of both clubs. EXCEPT keeps the rows of the first result that are absent from the second: the chess players who do not do music. EXCEPT is not symmetric: swapping the two queries changes the question.

Rows are compared as a whole: ('Claire', 'Paris') and ('Claire', 'Lyon') are two different rows, which UNION keeps both.

You can add a constant column to know where each row comes from: SELECT 'hotel' AS kind, name FROM hotels UNION ALL SELECT 'guest', name FROM guests.

Only one ORDER BY is allowed, at the very end: it sorts the combined result and uses the column names of the first SELECT.

Syntax

SELECT col
FROM a
UNION
SELECT col
FROM b; -- or UNION ALL, INTERSECT, EXCEPT

Worked example

SELECT member
FROM chess
UNION
SELECT member
FROM music;

All the members of at least one of the two clubs. Claire and David, who belong to both, appear only once.

Example result

Key points

• Same number of columns on both sides.
• UNION removes duplicates, UNION ALL keeps everything.
• INTERSECT = in both; EXCEPT = in the first but not in the second.

Common pitfalls

• An ORDER BY goes only once, at the very end, and applies to the combined result.
• EXCEPT is not symmetric: A EXCEPT B is not B EXCEPT A.

The level’s 5 exercises

1. Guided · Show the people who belong to both the chess club and the music club. (Tables: chess, music)
2. Practice · Show the people who belong to the chess club but not to the music club. (Tables: chess, music)
3. Practice · Show, without duplicates, every city that has a hotel or an airport. (Tables: hotels, airports)
4. Practice · Show in a single list the names of the hotels and the names of the guests, with a first column kind that is 'hotel' or 'guest'. (Tables: hotels, guests)
5. Challenge · All the hotels are in France. Show the city of each hotel and the city of each French airport, with a source column equal to 'hotel' or 'airport'. Sort the result by city then by source. A city appears once per hotel or airport: Paris has two hotels, so two rows “Paris, hotel”. (Tables: hotels, airports)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic