EXISTS and correlated subqueries
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — A subquery that looks outside
So far the subquery was independent: you could run it on its own.
A correlated subquery refers to the current row of the outer query — here t.trainer_id — so it is evaluated once per row:
SELECT t.name,
(SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = t.trainer_id) AS badge_count
FROM trainers t;For Alex the subquery counts the badges with trainer_id = 1, for Morgan = 2, and so on. Jordan gets 0, not NULL — and there is no GROUP BY and no LEFT JOIN.
Correlated = depends on the outer row
You cannot run SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = t.trainer_id alone: t does not exist outside the big query. That is the sign of a correlated subquery.
Also in UPDATE
Subqueries are not only for SELECT. UPDATE trainers SET badges = (SELECT COUNT(*) FROM badges bd WHERE bd.trainer_id = trainers.trainer_id); recomputes the badges column of every trainer from the real badge rows — one correlated subquery, one statement.
Exercise 2 — EXISTS: is there at least one?
EXISTS asks a yes/no question: does the subquery return at least one row? The trainers who won at least one battle:
SELECT name
FROM trainers t
WHERE EXISTS (SELECT 1 FROM battles b WHERE b.winner_id = t.trainer_id);What the subquery selects does not matter — SELECT 1 is the convention, since only the existence of rows counts. NOT EXISTS gives the opposite, and it is NULL-safe. The query that failed with NOT IN now works:
SELECT name
FROM trainers t
WHERE NOT EXISTS (SELECT 1 FROM codemon c WHERE c.trainer_id = t.trainer_id);Why NOT EXISTS ignores the wild Codémon
For Jordan, the subquery looks for Codémons with trainer_id = 4. Plantachu's NULL simply does not match 4 (NULL = 4 is unknown, so that row is filtered out), the subquery is empty and Jordan is kept. No list, no NULL poisoning the whole condition.
Exercise 3 — What EXISTS checks
What does EXISTS (subquery) evaluate to?
Hint : Existence is about rows, not values.
Exercise 4 — Each trainer's best Codémon
Complete the query so that it keeps, for each trainer, the Codémon whose level is the maximum of that trainer's team.
Hint : The inner alias must point back to the outer row's trainer.
Exercise 5 — Reading a correlated subquery
Here is a classic question: which Codémon is the strongest of its type? Pyrochu and Firochu are both fire type: only Pyrochu (level 14) should come out for fire.
With GROUP BY type you would get the maximum level per type — but not the name of the Codémon that has it. A correlated subquery solves this, because it keeps whole rows.
Read the query below as: "for each row c, keep it if its level equals the maximum level among the Codémons that have the same type as c":
SELECT name, type, level
FROM codemon c
WHERE level = (SELECT MAX(level) FROM codemon c2 WHERE c2.type = c.type)
ORDER BY type;Six types, six winners.
For the Pyrochu row, the subquery computes MAX(level) over the fire Codémons only: 14, which is Pyrochu's level, so Pyrochu stays. For the Firochu row, it computes the same maximum, 14 — but Firochu is level 9, so Firochu is dropped.
The only thing that changes from one row to the next is c.type. That is the correlation.
Two aliases for the same table
The outer query reads codemon as c; the subquery reads it again as c2. The two aliases must be different, otherwise SQL cannot tell which copy type refers to. c2.type = c.type is the link between the inner query and the current outer row.
Exercise 6 — Which tool?
Match each question with the kind of subquery that answers it best.
EXISTSNOT EXISTSExercise 7 — Count the evaluations
How many times is the subquery evaluated, and what does Jordan get?
SELECT t.name,
(SELECT COUNT(*) FROM battles b WHERE b.winner_id = t.trainer_id) AS wins
FROM trainers t;Hint : Correlated means once per outer row, and COUNT of nothing is 0.
Exercise 8 — Badges per gym, no JOIN
Professor Pi wants to know how many badges each gym has handed out — written with a correlated subquery, no JOIN and no GROUP BY.
Return name and badge_count for every gym. Sort by badge_count descending, then by name.
-- Write your query below