A common table expression is a named subquery introduced by a WITH clause. It does not change what SQL can compute — every CTE could be written as a derived table or a correlated subquery — but it changes how the query reads. The computation is stated once, given a name, and then referred to by that name, which is what turns a forty-line query into something a colleague can follow.
How do CTEs make complex queries readable?
The WITH clause goes before the main SELECT and introduces one or more named queries. Each name behaves like a temporary table that exists only for the duration of the statement:
WITH station_avg AS (
SELECT station_id, AVG(pm25) AS avg_pm25
FROM readings
GROUP BY station_id
)
SELECT s.name, ROUND(a.avg_pm25, 2) AS avg_pm25
FROM stations s JOIN station_avg a ON a.station_id = s.station_id
ORDER BY a.avg_pm25 DESC
LIMIT 6;
name avg_pm25
--------------- --------
Cathays Terrace 43.55
Clyde Walkway 34.44
Harbour Road 24.94
Roath Park 12.64
Clifton Hill 10.7
Kelvingrove 8.47
Compare that with the derived-table version from lesson 28: same numbers, completely different reading experience. The CTE says "first compute each station's average, then report it", and the main SELECT reads like a sentence about a table called station_avg that already exists.
What happens when one CTE feeds another?
CTEs can reference CTEs defined earlier in the same WITH clause, which is how a multi-step calculation becomes a chain of named steps. The per-station averages feed a per-city average, which feeds the final report:
WITH station_avg AS (
SELECT station_id, AVG(pm25) AS avg_pm25
FROM readings
GROUP BY station_id
),
city_avg AS (
SELECT s.city, AVG(a.avg_pm25) AS city_avg_pm25
FROM stations s JOIN station_avg a ON a.station_id = s.station_id
GROUP BY s.city
)
SELECT s.city, ROUND(c.city_avg_pm25, 2) AS city_avg_pm25
FROM city_avg c JOIN stations s ON s.city = c.city
GROUP BY s.city
ORDER BY c.city_avg_pm25 DESC;
city city_avg_pm25
------- --------------
Cardiff 28.1
Glasgow 21.45
Bristol 17.82
Two named steps and a final join. Written as nested subqueries the same logic would be a set of parentheses with the city-average subquery buried inside the station-average subquery — correct, and unreadable. The CTE version also documents intent: the names station_avg and city_avg are the vocabulary of the calculation, and a reader can check each step independently.
How does a CTE act as a filtered view?
Because a CTE can contain its own WHERE clause, it often exists to carve a subset out of a big table so that the rest of the query does not have to repeat the restriction. The March totals, isolated once:
WITH march AS (
SELECT station_id, SUM(pm25) AS march_pm25, COUNT(*) AS readings
FROM readings
WHERE recorded_on >= '2025-03-01' AND recorded_on < '2025-04-01'
GROUP BY station_id
)
SELECT s.name, m.readings, ROUND(m.march_pm25, 1) AS march_pm25
FROM stations s JOIN march m ON m.station_id = s.station_id
ORDER BY m.march_pm25 DESC;
name readings march_pm25
--------------- -------- ----------
Clyde Walkway 4 142.0
Cathays Terrace 3 135.7
Harbour Road 4 99.8
Roath Park 4 49.3
Clifton Hill 3 29.4
Kelvingrove 2 17.0
The date window appears once, at the top, where it can be read. Note that the same CTE shape can carry a different filter and answer a different question — swapping the date range for pm25 IS NOT NULL gives the worst reading per station, and swapping it for a station type gives the traffic-station picture. The shape is constant; the content changes.
What is the real advantage of a named CTE?
A CTE can be referenced more than once in the same statement, and that is where the named version earns its keep over a derived table. Ranking each station against the others uses the same per-station average twice — once for the row and once for the comparison:
WITH station_avg AS (
SELECT station_id, AVG(pm25) AS avg_pm25
FROM readings
GROUP BY station_id
)
SELECT s.name, ROUND(a.avg_pm25, 2) AS avg_pm25,
(SELECT COUNT(*) FROM station_avg b WHERE b.avg_pm25 < a.avg_pm25) AS stations_below
FROM stations s JOIN station_avg a ON a.station_id = s.station_id
ORDER BY a.avg_pm25 DESC;
name avg_pm25 stations_below
--------------- -------- --------------
Cathays Terrace 43.55 5
Clyde Walkway 34.44 4
Harbour Road 24.94 3
Roath Park 12.64 2
Clifton Hill 10.7 1
Kelvingrove 8.47 0
Each station is ranked by how many stations sit below it. Written with a derived table, the same query would need the aggregate written out twice — once in the FROM clause and once inside the scalar subquery — and the two copies would have to be kept in step by hand forever. The CTE states it once.
What should you know about CTEs before using them?
- A CTE is scoped to the statement that defines it. It is not a temp table, and nothing is stored on disk by definition.
- Most engines (SQLite included) may evaluate the CTE as a view and inline it into the plan, or materialise it once and reuse it. The standard does not require either.
- Referencing a CTE more than once is allowed, and materialising engines handle it well; a derived table repeated twice is duplicated work.
- CTEs cannot be referenced from a nested subquery in most engines — the WITH clause belongs at the top level of a statement.
- MySQL 8, PostgreSQL, SQL Server and Oracle all support the WITH clause; older MySQL versions did not.
- Recursive CTEs add the RECURSIVE keyword and are the subject of the next lesson.
The practical decision is simple. One sub-result used once: a derived table is fine. One sub-result used twice, or a query with several named stages: use CTEs. A result set that other queries and other people need: use a view (lesson 49), which is the permanent, database-wide version of exactly this idea.
Quick reference
- WITH introduces named subqueries that exist only for the statement that defines them.
- A CTE can reference earlier CTEs, turning a calculation into a chain of readable steps.
- A CTE with its own WHERE clause keeps a filter in one place instead of repeating it.
- Use a CTE when a result is reused or when the query has several stages; a view when it should be shared.
- Recursive CTEs add the RECURSIVE keyword after WITH.
Refactor drill: take a query that joins stations to readings and then rolls the result up by city — and rewrite it with two CTEs, one for the per-station figures and one for the per-city rollup — then count the lines the named version saves a reader who has to check the logic.