A subquery in the FROM clause is a derived table: a complete query treated as a table that the outer query can join to. It buys you the ability to aggregate in one step and then filter, join or re-aggregate the result in another — something a single flat SELECT cannot express without repeating the aggregate work in every expression.
What do subqueries in FROM buy you?
The pattern looks like this: parenthesise a full SELECT, give it a name with an alias, and treat that name as if it were a table you had created. The inner query is evaluated first, and the outer query sees only its output columns.
SELECT st.station_id, st.name, d.readings, d.avg_pm25
FROM stations st
JOIN (SELECT station_id, COUNT(*) AS readings, ROUND(AVG(pm25), 2) AS avg_pm25
FROM readings GROUP BY station_id) d
ON d.station_id = st.station_id
ORDER BY d.avg_pm25 DESC
LIMIT 6;
station_id name readings avg_pm25
--------- --------------- -------- --------
3 Cathays Terrace 4 43.55
5 Clyde Walkway 6 34.44
1 Harbour Road 5 24.94
4 Roath Park 5 12.64
2 Clifton Hill 4 10.7
6 Kelvingrove 3 8.47
The derived table d is a per-station summary: how many readings, and what the average PM2.5 was. The outer query joins that summary to the station metadata and sorts by it. Without the derived table you would have to repeat the GROUP BY inside every aggregate you wanted over it, or settle for a join that produces one row per reading.
Why put the filter inside the derived table?
A derived table can carry its own WHERE clause, and that is where its real value appears. The query below asks for the worst reading at each station, considering only March:
SELECT st.name, d.max_pm25
FROM stations st
JOIN (SELECT station_id, MAX(pm25) AS max_pm25
FROM readings
WHERE recorded_on >= '2025-03-01' AND recorded_on < '2025-04-01'
AND pm25 IS NOT NULL
GROUP BY station_id) d
ON d.station_id = st.station_id
WHERE d.max_pm25 > 40
ORDER BY d.max_pm25 DESC;
name max_pm25
--------------- --------
Cathays Terrace 51.9
Clyde Walkway 42.1
Two filters, two different levels. The month restriction and the pm25 IS NOT NULL sit inside, because they concern individual readings before the MAX is computed; the max_pm25 > 40 sits outside, because it concerns a finished summary row. Moving the first filter outside would not work — there is no MAX column to filter on at the reading level — and moving the second inside would change its meaning to "readings above 40", not "stations whose worst reading was above 40".
The pm25 IS NOT NULL deserves a second look. MAX ignores NULL inputs anyway, and the only missing PM2.5 value in this dataset falls in April, so the March result would be identical without it. But a derived table computing MIN or SUM would behave differently, and being explicit about which rows are missing from the calculation is cheap insurance.
What happens when you aggregate an aggregate?
Here is the payoff of a derived table: it lets you aggregate the result of an aggregate. The average of the per-station averages is not the same number as the average of all readings, because the two weight their inputs differently:
SELECT ROUND(AVG(pm25), 2) AS flat_average,
(SELECT ROUND(AVG(avg_pm25), 2)
FROM (SELECT AVG(pm25) AS avg_pm25 FROM readings GROUP BY station_id)) AS average_of_averages
FROM readings;
flat_average average_of_averages
------------ -------------------
23.17 22.46
The flat average weights every reading equally: Clyde Walkway, with six readings, pulls the number up more than Clifton Hill with four. The average of averages gives each station one vote, and comes out lower. Neither is wrong — they answer different questions — and only a derived table can produce both in one statement. The choice between them is a real analytical decision, and it is worth stating in the report which one a figure represents.
What are the rules and the costs?
- An alias is mandatory. The derived table has no name of its own, so
JOIN (SELECT ...) ON ...is a syntax error without one. - The derived table is a real table for the outer query: it has columns, and they can be filtered, joined and aggregated.
- Column names in the outer query come from the inner SELECT. Only aliased or computed columns are available — you cannot reference
pm25outside unless you selected it. - Some engines materialise the derived table before the outer query runs, others fold it into the outer plan. On large data the difference is the difference between one pass and two.
- MySQL, SQL Server and Oracle have their own syntax for the same idea — a derived table with an alias is the portable spelling.
And the important comparison: a derived table is scoped to a single query. The moment you want to use the same summary twice, or name it for a colleague to read, you want a CTE (lesson 30) or a view (lesson 49). The three are the same idea at different scopes — a named result set, a named result set for one query, and a named result set for the whole database.
Quick reference
- A derived table is a parenthesised SELECT in FROM with a mandatory alias.
- It is evaluated before the outer query, so its output can be filtered, joined and re-aggregated.
- Filters inside the derived table concern rows; filters outside concern summary rows.
- Average-of-averages and flat average weight their inputs differently and answer different questions.
- Reach for a CTE when the same sub-result is used more than once, and a view when it should be reusable across queries.
Derived-table drill: write a derived table that returns, for each city, the number of stations and the worst single PM2.5 reading recorded there — then join it to the stations table and list the cities whose worst reading exceeds 40.