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, with its station's maximum, with its city's average, with the next row above it in a ranking.
The previous lesson in this module covered "What Do Subqueries in FROM Buy You?", and the next lesson in this module is about "How Do CTEs Make Complex Queries Readable?".
How do the correlated subqueries reference the outer rows?
The mechanism is a column reference that has no meaning inside the subquery. Here the inner query names the r2.station_id, which belongs to the inner alias, and the r.station_id, which belongs to the outer alias. 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.9Every reading of Harbour Road carries the same station maximum, which is the 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, and that is exactly what "correlated" means. The answer of the inner query depends on the outer row.
How do you compare a row with its own group average?
The same mechanism reaches one level further. It joins inside the subquery, so that the comparison can be made against a whole city rather than against 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.61Here the correlation is two levels deep. The inner query joins the readings to the stations, and then it filters on the s.city of the outer row.
The traffic station of Cardiff reads far above the Cardiff average of the 26.38, while the background station of Bristol reads below the Bristol average of the 18.61. That is the whole point of a network with the 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 the 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 WalkwayTwo of the stations qualify. Notice 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 the correlation cost?
Here is the trade-off. A correlated subquery is re-executed for every outer row, so a query over the ten thousand readings performs the ten thousand inner lookups. The same question that is written with a join and a GROUP BY does the work in a single pass.
"Top reading per station" as a correlated subquery, which is the condition WHERE r.pm25 = (SELECT MAX(r2.pm25) FROM readings r2 WHERE r2.station_id = r.station_id), is readable and correct, and it can be slow on a large table. The same answer with a join plus a GROUP BY, or with a window function in module 11, is usually faster because the engine reads the readings table once.
The correlated form wins on the clarity when the data is small, and it wins on the correctness when the grouping is awkward to express otherwise.
There is a third option, which is a derived table or a CTE that computes the group maximum once and 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.1That last query is the correlated pattern used as a filter, and it returns one row per station. That is the worst day that 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.
You learn the correlated form first, because it is the most direct expression of the idea. Then you reach for the faster form when the data grows.
Quick reference
- A correlated subquery references a column of the outer query, and it is re-run for every outer row.
- It is the direct way to compare a row with a property of its own group, whether that is the maximum, the average or the rank.
- The correlated EXISTS is a filter and not a value, and it short-circuits on the first matching row.
- The cost is one inner execution for every outer row, so you prefer a join or a window function on the large tables.
- You use a different alias for every table in the chain, and the alias names are how the correlation is expressed.
Correlation drill: you write a query that lists every reading that was dirtier than the average for its own station, using a correlated subquery. Then you rewrite the same query with a join to a per-station average, and you compare the two results row by row.