A common table expression is a named subquery that is introduced by a WITH clause. It does not change what the SQL can compute, because every CTE could be written as a derived table or as a correlated subquery. It changes how the query reads. The computation is stated once, it is given a name, and then it is referred to by that name. That is what turns a forty-line query into something that a colleague can follow.
The previous lesson in this module covered "How Do Correlated Subqueries Reference Outer Rows?", and the next lesson in this module is about "What Do Recursive CTEs Traverse?".
How do the CTEs make the complex queries readable?
The WITH clause goes before the main SELECT, and it introduces one or more named queries. Each of the names 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.47Compare that with the derived-table version from lesson 28. The same numbers and a 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?
The CTEs can reference the CTEs that were defined earlier in the same WITH clause, and that is how a multi-step calculation becomes a chain of the 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.82There are two named steps and a final join.
Written as the nested subqueries, the same logic would be a set of the parentheses with the city-average subquery buried inside the station-average subquery. It would be correct and it would be unreadable.
The CTE version also documents the intent. The names station_avg and city_avg are the vocabulary of the calculation, and a reader can check each of the steps 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. Here are 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.0The date window appears once, at the top, where it can be read.
Notice that the same CTE shape can carry a different filter and answer a different question. Swapping the date range for the 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 and 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 0Each of the stations is ranked by how many of the 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 for ever.
The CTE states it once.
What should you know about the CTEs before you use them?
- A CTE is scoped to the statement that defines it. It is not a temp table, and nothing is stored on the disk by the definition.
- Most of the engines, SQLite included, may evaluate the CTE as a view and inline it into the plan, or they may materialise it once and reuse it. The standard does not require either one.
- Referencing a CTE more than once is allowed, and the materialising engines handle it well. A derived table that is repeated twice is the duplicated work.
- The CTEs cannot be referenced from a nested subquery in most of the engines, because the WITH clause belongs at the top level of a statement.
- MySQL 8, PostgreSQL, the SQL Server and Oracle all support the WITH clause. The older MySQL versions did not.
- The recursive CTEs add the RECURSIVE keyword, and they are the subject of the next lesson.
The practical decision is simple. For the one sub-result that is used once, a derived table is fine. For the one sub-result that is used twice, or for a query with the several named stages, you use the CTEs. For a result set that the other queries and the other people need, you use a view in lesson 49, which is the permanent and the database-wide version of exactly this idea.
Quick reference
- The WITH introduces the named subqueries that exist only for the statement that defines them.
- A CTE can reference the earlier CTEs, and that turns 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.
- You use a CTE when a result is reused or when the query has the several stages, and a view when it should be shared.
- The recursive CTEs add the RECURSIVE keyword after the WITH.
Refactor drill: you take a query that joins the stations to the readings and then rolls the result up by the city, and you rewrite it with the two CTEs. There is one for the per-station figures and one for the per-city rollup. Then you count the lines that the named version saves for a reader who has to check the logic.