A subquery in the FROM clause is a derived table. It is a complete query that is 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 step, which is something that a single flat SELECT cannot express without repeating the aggregate work in every expression.
The previous lesson in this module covered "How Do Subqueries Inside WHERE Compare Sets?", and the next lesson in this module is about "How Do Correlated Subqueries Reference Outer Rows?".
What do the subqueries in FROM buy you?
The pattern looks like this. You parenthesise a full SELECT, you give it a name with an alias, and you treat that name as if it were a table that 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.47The derived table d is a per-station summary. It holds how many readings there were, and what the average PM2.5 was. The outer query joins that summary to the station metadata and it sorts by it.
Without the derived table you would have to repeat the GROUP BY inside every aggregate that you wanted over it, or you would settle for a join that produces one row for each reading.
Why do you 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 the month of 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.1There are two filters at two different levels. The month restriction and the pm25 IS NOT NULL sit inside, because they concern the 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, because there is no MAX column to filter on at the reading level. And moving the second one inside would change its meaning into "readings above 40" and not "stations whose worst reading was above 40".
The pm25 IS NOT NULL deserves a second look. The MAX ignores the 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 that computes the MIN or the 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 of the readings, because the two of them weigh 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.46The flat average weighs every reading equally. Clyde Walkway, with its six readings, pulls the number up more than Clifton Hill with its four does. The average of the averages gives each station one vote, and it comes out lower.
Neither of them is wrong. They answer the different questions, and only a derived table can produce both of them in one statement. The choice between them is a real analytical decision, and it is worth stating in the report which one of them a figure represents.
What are the rules and the costs?
An alias is mandatory. The derived table has no name of its own, so the JOIN (SELECT ...) ON ... is a syntax error without one.
The derived table is a real table for the outer query. It has the columns, and they can be filtered, joined and aggregated.
The column names in the outer query come from the inner SELECT. Only the aliased or the computed columns are available, so you cannot reference the pm25 outside unless you selected it.
Some of the engines materialise the derived table before the outer query runs, and the others fold it into the outer plan. On the large data the difference is the difference between one pass and two.
MySQL, the SQL Server and Oracle have their own syntax for the same idea, and a derived table with an alias is the portable spelling.
And there is the important comparison. A derived table is scoped to a single query. The moment that you want to use the same summary twice, or you want to name it for a colleague to read, you want a CTE in lesson 30 or a view in lesson 49. The three of them are the same idea at the different scopes. There is a named result set, there is a named result set for the one query, and there is a named result set for the whole database.
Quick reference
- A derived table is a parenthesised SELECT in the FROM with a mandatory alias.
- It is evaluated before the outer query, so its output can be filtered, joined and re-aggregated.
- The filters inside the derived table concern the rows, and the filters outside concern the summary rows.
- The average of the averages and the flat average weigh their inputs differently and they answer the different questions.
- You reach for a CTE when the same sub-result is used more than once, and a view when it should be reusable across the queries.
Derived-table drill: you write a derived table that returns, for each city, the number of the stations and the worst single PM2.5 reading that was recorded there. Then you join it to the stations table and you list the cities whose worst reading exceeds the 40.