INTERSECT and EXCEPT
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 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;plantwater, fire, rock, electric, ghostHint : 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.
UNIONUNION ALLINTERSECTEXCEPTExercise 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;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.
-- Write your query below