EXISTS and correlated subqueries

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

▶ Do the exercises

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

Options
• True as soon as the subquery returns at least one row, whatever the row contains
• True only if the subquery returns exactly one row
• The number of rows returned by the subquery
• True if the first value returned is not NULL

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.

Items to match
• Trainers who won at least one battle
• Trainers who never won, safe even with NULLs
• Each trainer's number of badges, as a column
• Codémons above the overall average level
Definitions
• EXISTS
• NOT EXISTS
• Correlated subquery in SELECT
• Scalar subquery, not correlated

Exercise 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;
Options
• 4 times, once per trainer; Jordan gets 0
• Once; Jordan gets 0
• 4 times; Jordan gets NULL
• 6 times, once per battle; Jordan is not listed

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.

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