A correlated subquery is one that reaches back into the query that contains it. Its inner FROM clause references a column of the outer query, so the inner query cannot be evaluated on its own: it is re-run for every outer row. That is what lets a row be compared with its own group — its station's maximum, its city's average, the next row above it in a ranking.
How do correlated subqueries reference outer rows?
The mechanism is a column reference that has no meaning inside the subquery. Here the inner query names r2.station_id, which belongs to the inner alias, and r.station_id, which belongs to the outer alias — and it is that second reference that makes the two rows relate:
SELECT s.name, r.recorded_on, r.pm25,
(SELECT MAX(r2.pm25) FROM readings r2 WHERE r2.station_id = r.station_id) AS station_max
FROM stations s JOIN readings r ON r.station_id = s.station_id
WHERE r.station_id IN (1, 3)
ORDER BY r.station_id, r.pm25 DESC;
name recorded_on pm25 station_max
--------------- ----------- ---- -----------
Harbour Road 2025-03-15 31.2 31.2
Harbour Road 2025-03-22 27.5 31.2
Harbour Road 2025-04-07 24.9 31.2
Harbour Road 2025-03-08 22.7 31.2
Harbour Road 2025-03-01 18.4 31.2
Cathays Terrace 2025-03-08 51.9 51.9
Cathays Terrace 2025-03-01 44.6 51.9
Cathays Terrace 2025-03-15 39.2 51.9
Cathays Terrace 2025-04-06 38.5 51.9
Every Harbour Road reading carries the same station maximum, 31.2, because the inner query keeps returning the maximum for whichever station the outer row belongs to. The value is computed once per outer row, which is exactly what "correlated" means: the inner query's answer depends on the outer row.
How do you compare a row with its own group average?
The same mechanism reaches one level further, joining inside the subquery so that the comparison can be made against a whole city rather than a single station:
SELECT s.city, s.name, r.recorded_on, r.pm25,
(SELECT ROUND(AVG(r3.pm25), 2) FROM readings r3
JOIN stations s3 ON s3.station_id = r3.station_id WHERE s3.city = s.city) AS city_avg
FROM stations s JOIN readings r ON r.station_id = s.station_id
WHERE r.station_id IN (2, 3)
ORDER BY s.name, r.pm25;
city name recorded_on pm25 city_avg
------- --------------- ----------- ---- --------
Cardiff Cathays Terrace 2025-04-06 38.5 26.38
Cardiff Cathays Terrace 2025-03-15 39.2 26.38
Cardiff Cathays Terrace 2025-03-01 44.6 26.38
Cardiff Cathays Terrace 2025-03-08 51.9 26.38
Bristol Clifton Hill 2025-03-15 8.4 18.61
Bristol Clifton Hill 2025-03-01 9.8 18.61
Bristol Clifton Hill 2025-03-08 11.2 18.61
Bristol Clifton Hill 2025-04-05 13.4 18.61
Here the correlation is two levels deep: the inner query joins readings to stations, then filters on the outer row's s.city. Cardiff's traffic station reads far above the Cardiff average of 26.38 while Bristol's background station reads below the Bristol average of 18.61 — which is the whole point of a network with mixed station types.
What does a correlated EXISTS buy you?
The most common correlated subquery is the existence check, and it is a filter rather than a value. Which stations have ever recorded a reading above 40?
SELECT s.station_id, s.name
FROM stations s
WHERE EXISTS (SELECT 1 FROM readings r WHERE r.station_id = s.station_id AND r.pm25 > 40)
ORDER BY s.station_id;
station_id name
----------- --------------
3 Cathays Terrace
5 Clyde Walkway
Two stations qualify. Note that the subquery does not select a value — it selects the constant 1 — because only the existence of a matching row matters. This is the shape to reach for whenever the question is "does this row have any children that satisfy a condition", and it is the most efficient of the subquery forms because the engine stops at the first match.
What does correlation cost?
Here is the trade-off. A correlated subquery is re-executed for every outer row, so a query over ten thousand readings performs ten thousand inner lookups. The same question written with a join and a GROUP BY does the work in a single pass:
- "Top reading per station" as a correlated subquery —
WHERE r.pm25 = (SELECT MAX(r2.pm25) FROM readings r2 WHERE r2.station_id = r.station_id)— is readable and correct, and can be slow on a large table. - The same answer with a join plus GROUP BY, or with a window function (module 11), is usually faster because the engine reads the readings table once.
- The correlated form wins on clarity when the data is small, and on correctness when the grouping is awkward to express otherwise.
A third option is a derived table or CTE that computes the group maximum once, then joins it back:
SELECT s.name, r.recorded_on, r.pm25
FROM stations s JOIN readings r ON r.station_id = s.station_id
WHERE r.pm25 = (SELECT MAX(r2.pm25) FROM readings r2 WHERE r2.station_id = r.station_id)
ORDER BY r.pm25 DESC;
name recorded_on pm25
--------------- ----------- ----
Cathays Terrace 2025-03-08 51.9
Clyde Walkway 2025-03-10 42.1
Harbour Road 2025-03-15 31.2
Roath Park 2025-03-16 14.1
Clifton Hill 2025-04-05 13.4
Kelvingrove 2025-03-12 9.1
That last query is the correlated pattern used as a filter, and it returns one row per station — the worst day each station recorded. Recognise the shape: whenever the outer query needs to compare a row against a property of its own group, you have a choice between the correlated subquery, the grouped join and, later, the window function. Learn the correlated form first because it is the most direct expression of the idea, then reach for the faster form when the data grows.
Quick reference
- A correlated subquery references a column of the outer query and is re-run for every outer row.
- It is the direct way to compare a row with a property of its own group — maximum, average, rank.
- Correlated EXISTS is a filter, not a value, and short-circuits on the first matching row.
- The cost is one inner execution per outer row, so prefer a join or window function on large tables.
- Use a different alias for every table in the chain; the alias names are how the correlation is expressed.
Correlation drill: write a query that lists every reading that was dirtier than the average for its own station — using a correlated subquery — then rewrite the same query with a join to a per-station average and compare the two results row by row.