INTERSECT and EXCEPT

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

▶ Do the exercises

Exercise 1 — INTERSECT: in both lists

Look at this image, you know the two on top. The bottom row is this chapter:

INTERSECT keeps only the values that appear in both results. The cities that have a trainer and a gym:

SELECT hometown AS city FROM trainers INTERSECT SELECT city FROM gyms ORDER BY city;

Same shape rules as UNION (same number of columns, same order), and the result is always distinct. Moonlight Hollow has a gym but no trainer, so it is out.

Exercise 2 — EXCEPT: in the first, not in the second

EXCEPT keeps the values of the first result that are absent from the second. Cities with a gym but no trainer:

SELECT city FROM gyms EXCEPT SELECT hometown FROM trainers;

Order matters. Swap the two SELECTs and you ask a different question — cities with a trainer but no gym — whose answer is empty:

SELECT hometown FROM trainers EXCEPT SELECT city FROM gyms;

Exercise 3 — First minus second

Codémon types: water, electric, fire, ghost, plant, rock. Gym types: water, fire, rock, electric, ghost. What does this query return?

SELECT type FROM codemon EXCEPT SELECT type FROM gyms;
Options
• plant
• No rows
• All six Codémon types
• water, fire, rock, electric, ghost

Hint : The first list minus everything that is in the second.

Exercise 4 — EXCEPT as an anti-join

EXCEPT is another way to find missing rows, next to LEFT JOIN … IS NULL. The trainers who never won a battle:

SELECT trainer_id FROM trainers EXCEPT SELECT winner_id FROM battles;

The result is a distinct list of values — here an id. To get names instead, make both sides return names: the second SELECT can be a join.

SELECT name FROM trainers EXCEPT SELECT t.name FROM trainers t INNER JOIN battles b ON b.winner_id = t.trainer_id;

Which tool for missing rows?

LEFT JOIN … IS NULL when you need columns from both tables or a count; EXCEPT when you compare two plain lists of values. EXCEPT is also relaxed about NULL: two NULLs count as the same value.

Exercise 5 — Types with a gym

Complete the query so that it lists the Codémon types for which at least one gym exists.

Hint : Present in both lists, and both lists must contain types.

Exercise 6 — Four operators

Match each operator with what it keeps.

Items to match
• UNION
• UNION ALL
• INTERSECT
• EXCEPT
Definitions
• All values from both lists, without duplicates
• All rows from both lists, duplicates kept
• Only the values present in both lists
• The values of the first list that are not in the second

Exercise 7 — Taylor's exclusives

Taylor (trainer_id 3) owns plant, rock and fire Codémons; the other trainers own water, electric, fire and ghost ones. What does this query return?

SELECT type FROM codemon WHERE trainer_id = 3 EXCEPT SELECT type FROM codemon WHERE trainer_id <> 3;
Options
• plant and rock
• fire
• plant, rock and fire
• No rows

Hint : Taylor's types minus everyone else's. Fire is shared with Morgan.

Exercise 8 — Alex's weak spots

Professor Pi is coaching Alex (trainer_id 1) and wants the gym types Alex cannot match with a Codémon of the same type.

Return the types of gyms for which Alex owns no Codémon of that type. Use EXCEPT. Sort by type.

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