SQL / SUBQUERIES AND CTES
Table subqueries in the FROM clause
Use a subquery in the FROM clause as an intermediate table so the outer query can filter, group, and join results one query level cannot produce.
What you will learn
- Aggregate inside a FROM subquery, then filter that result in the outer query
- Reference only the columns the subquery projects, under the names it gives them
- Join two FROM subqueries grouped at different levels to compare their totals
- Use ORDER BY with LIMIT inside a FROM subquery to fix a slice before aggregating
Understanding Table subqueries in the FROM clause
FROM does not require a stored table; it requires something that yields rows and columns. A parenthesized SELECT qualifies, so FROM (SELECT ...) AS t hands the rest of the statement a finished result set that behaves exactly like a table. That inner query is self-contained: it is evaluated on its own terms, without knowing which outer row is being processed, and the outer query works only with the rows it produced.
The reason this matters is clause order inside a single SELECT: WHERE filters raw rows, GROUP BY collapses them, HAVING filters the groups. That order is why WHERE SUM(amount) >= 300 is rejected and why SUM(COUNT(*)) is not a legal expression, since one SELECT gives you one aggregation step. A table subquery buys you a second step: the inner query finishes its grouping, and the outer query starts over with those rows, where rep_total is now an ordinary integer column that WHERE, GROUP BY and JOIN can all use.
Think of the subquery's SELECT list as a wall with named holes in it. Only what the list projects gets through, under the name it was given, which is why amount vanishes the moment it is wrapped in SUM and why computed columns need an alias to be usable above. Rows cross that wall as an unordered set, so an ORDER BY inside changes only which rows survive a LIMIT, not the order you finally see; the outermost ORDER BY decides that.
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
region TEXT,
rep TEXT,
amount INTEGER
);
INSERT INTO sales (id, region, rep, amount) VALUES
(1, 'north', 'ana', 400),
(2, 'north', 'ana', 350),
(3, 'north', 'bo', 120),
(4, 'south', 'cy', 900),
(5, 'south', 'cy', 150),
(6, 'south', 'dee', 80),
(7, 'east', 'eli', 300),
(8, 'north', 'bo', 250),
(9, 'north', 'fay', 90);
SELECT region,
COUNT(*) AS strong_reps,
SUM(rep_total) AS strong_total
FROM (
SELECT region, rep, SUM(amount) AS rep_total
FROM sales
GROUP BY region, rep
) AS per_rep
WHERE rep_total >= 300
GROUP BY region
ORDER BY region;A subquery in FROM is a complete query whose finished result set becomes the outer query's input, exposing only the columns it projects.
Worked examples
Joining two grouped subqueries
Two FROM subqueries aggregated at different levels are joined so each rep's total sits next to its region's total (reuses the sales table above).
SELECT r.region,
r.rep,
r.rep_total,
t.region_total,
100 * r.rep_total / t.region_total AS pct
FROM (
SELECT region, rep, SUM(amount) AS rep_total
FROM sales
GROUP BY region, rep
) AS r
JOIN (
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region
) AS t ON t.region = r.region
ORDER BY r.region, r.rep;Example explained
Line 1The first subquery groups by region and rep, so it has one row per rep; the second groups by region only, so it has one row per region.
Line 2ON t.region = r.region repeats the coarse region total beside every rep row, which a single GROUP BY cannot do because one query has one grain at a time.
Line 3r. and t. are needed to qualify region because both result sets project a column with that name.
Line 4100 * r.rep_total / t.region_total is integer division, so 750 out of 1210 truncates to 61 instead of rounding to 62.
Limiting rows before aggregating
ORDER BY with LIMIT inside the FROM subquery decides which rows exist before the outer aggregate ever runs.
SELECT COUNT(*) AS rows_kept, SUM(amount) AS top_total
FROM (
SELECT amount
FROM sales
ORDER BY amount DESC
LIMIT 3
) AS top3;Example explained
Line 1The subquery sorts all nine sales and keeps three rows, so the outer SUM only ever sees 900, 400 and 350.
Line 2One query level cannot express this: there LIMIT applies to the final rows, after aggregation, so SUM would already have collapsed all nine sales.
Line 3COUNT(*) returns 3, showing the outer query counts the subquery's rows and not the base table's.
Line 4The amounts are all distinct here, so LIMIT 3 is deterministic; with a tie at third place the surviving row would be arbitrary.
Important notes
A plain FROM subquery cannot reference columns of other tables listed in the same FROM clause; that needs LATERAL or CROSS APPLY.
LIMIT inside a derived table works in SQLite, PostgreSQL and MySQL 8; SQL Server needs TOP or OFFSET ... FETCH, and Oracle needs FETCH FIRST.
Common mistakes
Writing WHERE amount > 100 in the outer query when the subquery projected only region, rep and rep_total: the statement fails with 'no such column: amount', because amount was consumed by SUM and never crossed the projection.
Leaving SUM(amount) unaliased inside the subquery, so the outer query has no dependable name to reference and you end up guessing at an engine-generated label like 'SUM(amount)'.
Trusting an ORDER BY inside the subquery to order the final result: without an outer ORDER BY the rows may come back in any order, and planners are free to drop the inner sort when no LIMIT depends on it.
Try it yourself
Change, predict, then run
Create a table scores(student, subject, points) with about ten rows, then use a FROM subquery to total points per student and have the outer query report how many students exceed 200 points and what their points add up to.
Open the SQL workspaceCheck your understanding
A FROM subquery groups sales by rep and projects rep and SUM(amount) AS rep_total. The outer query adds WHERE amount > 100. What happens?
- An error, because amount is not one of the columns the subquery projects
- The base rows are filtered first, so each rep_total covers only sales above 100
- rep_total is compared instead, since it was computed from amount
- The condition is silently ignored and every rep is returned
Show answer
The subquery's SELECT list defines everything visible above it, and amount was consumed by SUM without being projected, so the name does not resolve. The second option describes what would happen if the outer WHERE ran before the inner GROUP BY, but the outer query only ever sees rows the subquery has already finished producing; to filter before grouping you must move the condition inside the subquery.