SQL · Guides · October 30, 2026 · 13 min read

SQL CTE (WITH clause): write queries in readable steps

A CTE (common table expression) is a named, temporary result that exists only for one query. You define it with WITH name AS (SELECT ...) and then use name in the main query as if it were a table. Nothing new happens under the hood that a subquery could not do. The point is readability: instead of nesting queries three levels deep, you write them top to bottom, one step per CTE, and give each step a name that says what it holds.

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ... FROM step_two;

The examples run on the airline database from the Nomto SQL course: 33,121 flights between 104 airports in summer 2026, with bookings, tickets, seats and a loyalty programme. The SQL is PostgreSQL syntax and every result shown was produced by running the query.

Your first CTE

How many tickets do loyalty members typically have? That needs two levels of aggregation: tickets per member, then members per ticket count. With a CTE, each level gets its own block:

WITH per_member AS (
  SELECT member_id, COUNT(*) AS tickets
  FROM ticket_members
  GROUP BY member_id
)
SELECT tickets, COUNT(*) AS members
FROM per_member
GROUP BY tickets
ORDER BY tickets
LIMIT 8;

per_member holds one row per member who has tickets. The main query reads it like a table. Exactly the same logic fits in a derived table (FROM (SELECT ...) AS per_member), and for one step that is fine. The difference shows once there are several steps.

(The bump at seven and eight tickets, after the numbers had been falling, is in the data, not a bug in the query. It is worth a closer look before anyone builds a chart on it, and a two-step query like this is often how you spot such things.)

Several CTEs: one step per idea

What is the average load factor (passengers divided by seats) of arrived flights, per aircraft model? Three ingredients are needed: seats per model, passengers per flight, and the flights themselves. Each becomes a CTE, and later CTEs can use earlier ones:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax
  FROM ticket_flights
  GROUP BY flight_id
),
flight_load AS (
  SELECT f.flight_id,
         f.aircraft_code,
         COALESCE(p.pax, 0) AS pax,
         s.seats,
         COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
  LEFT JOIN passengers p ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
)
SELECT aircraft_code,
       COUNT(*) AS flights,
       MAX(seats) AS seats,
       ROUND(AVG(pax), 1) AS avg_pax,
       ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC;

Each CTE does one thing, and the final SELECT only summarises. Pre-aggregating seats and passengers before the join is also what keeps the numbers correct: joining flights to raw seats and raw ticket_flights would multiply rows and inflate every count.

Here is the same query written with nested derived tables (shortened to the first three rows):

SELECT aircraft_code, COUNT(*) AS flights, ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM (
  SELECT f.aircraft_code, COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN (SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code) s
    ON s.aircraft_code = f.aircraft_code
  LEFT JOIN (SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id) p
    ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
) AS flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC
LIMIT 3;

Same answer. But you have to read it from the inside out, and the steps have no names. In a review, “check passengers” is a lot easier to say than “check the second subquery inside the first one”.

Debugging a CTE chain step by step

The real advantage of named steps: you can inspect any of them by swapping the final SELECT. Replace it with SELECT * FROM flight_load LIMIT 20, or with a count, and you see exactly what that step produces.

That habit catches mistakes like this one. Change the LEFT JOIN passengers to a plain JOIN and the load factors jump:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100 * AVG(p.pax * 1.0 / s.seats), 1) AS avg_load_pct
FROM flights f
JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
JOIN passengers p ON p.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY avg_load_pct DESC;

The Cessna’s load factor goes from 14% to 50%, because its 3,328 empty flights were silently dropped. A quick check on the passengers step shows why: of 16,707 arrived flights, only 11,438 have any tickets. An inner join keeps only those. Neither version is “the” answer by itself, but you need to know which question you are answering, and named steps make that check a one-liner.

Using a CTE more than once

A CTE can be referenced several times in the main query, which a derived table cannot do without being copied. A classic use: a value and its share of the total.

WITH route_revenue AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
         SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.status = 'Arrived'
  GROUP BY route
)
SELECT route,
       ROUND(revenue) AS revenue,
       ROUND(100 * revenue / (SELECT SUM(revenue) FROM route_revenue), 2) AS pct_of_total
FROM route_revenue
ORDER BY revenue DESC
LIMIT 5;

route_revenue is used twice: as the main table and inside the scalar subquery for the total. Across all 451 routes, arrived flights brought in $114.3 million, and the top route, Domodedovo to Khabarovsk, earned almost 4% of it.

A CTE can also hold a one-row summary that you join to every row. Which booking days were busiest compared with the average day?

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  GROUP BY day
),
stats AS (
  SELECT AVG(bookings) AS avg_bookings FROM daily
)
SELECT d.day, d.bookings, ROUND(d.bookings / s.avg_bookings, 2) AS vs_avg
FROM daily d
CROSS JOIN stats s
ORDER BY d.bookings DESC
LIMIT 5;

stats reads from daily, and the main query reads from both. (The average, 4,692.6 bookings a day, is over 56 days from 17 June to 11 August; the last day is only partly in the data, which pulls the average down a little. For this kind of comparison you would usually drop incomplete days.)

Naming the columns of a CTE

You can list column names after the CTE name. They override whatever the inner query calls its columns:

WITH model_seats (code, seat_count) AS (
  SELECT aircraft_code, COUNT(*)
  FROM seats
  GROUP BY aircraft_code
)
SELECT code, seat_count
FROM model_seats
ORDER BY seat_count DESC
LIMIT 3;

Most people alias inside the SELECT instead (COUNT(*) AS seat_count). The column list is a tidy option when the inner query has unnamed expressions, and it keeps the CTE’s interface visible in its first line.

Recursive CTEs

WITH RECURSIVE lets a CTE refer to itself. It has two parts joined by UNION or UNION ALL: an anchor that produces the starting rows, and a recursive part that produces new rows from the rows of the previous round. It stops when a round produces nothing new.

The smallest example generates numbers:

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

The WHERE i < 5 is the stop condition. Without it, the query never ends. (For numbers and dates, generate_series is simpler; see the date spine guide.)

Recursion earns its keep on graphs and hierarchies. How many flights does it take to get from Yuzhno-Sakhalinsk (UUS) to other airports in the network?

WITH RECURSIVE routes AS MATERIALIZED (
  SELECT DISTINCT departure_airport AS src, arrival_airport AS dst
  FROM flights
),
reach AS (
  SELECT 'UUS'::VARCHAR AS airport, 0 AS hops
  UNION
  SELECT r.dst, reach.hops + 1
  FROM reach
  JOIN routes r ON r.src = reach.airport
  WHERE reach.hops < 4
),
best AS (
  SELECT airport, MIN(hops) AS hops
  FROM reach
  GROUP BY airport
)
SELECT hops, COUNT(*) AS airports
FROM best
GROUP BY hops
ORDER BY hops;

From UUS there are four direct destinations, 76 more airports need one change, and the last 23 need two. Nobody needs a fourth flight: the search was allowed to go four hops deep and found nothing new beyond three, so every airport in the schedule is reachable from UUS in at most three flights.

Four details make this query safe:

• WITH RECURSIVE is written once, at the top, even though only reach is recursive. Ordinary CTEs (routes, best) can sit in the same WITH.
• UNION (not UNION ALL) removes duplicate (airport, hops) rows, which stops the rows from exploding when many paths lead to the same airport.
• hops < 4 is a hard depth limit. On a graph with cycles (and routes go both ways), recursion without a limit can run forever.
• routes AS MATERIALIZED computes the route list once, before the recursion starts. On DuckDB (the engine behind the course playground) the same query without it sometimes stops early and returns only the first two rows; with MATERIALIZED the result is stable. PostgreSQL accepts the keyword too.

CTE vs subquery vs temporary table vs view

Use a CTE for steps inside one analysis. If the same intermediate result feeds several separate queries, a temporary table saves recomputing it. If a whole team needs the logic, it belongs in a view or a model in your data pipeline.

Do CTEs make queries faster or slower?

Neither, by default. In PostgreSQL 12 and later, a non-recursive CTE that is referenced once is inlined into the main query, exactly like a subquery, and the optimizer sees through it. A CTE referenced more than once is usually computed once and reused.

You can override that choice. WITH big AS MATERIALIZED (...) forces PostgreSQL to compute the CTE once and keep it, which helps when the CTE is expensive and the optimizer keeps recomputing it. NOT MATERIALIZED forces inlining, so filters from the main query can be pushed inside. DuckDB accepts the same MATERIALIZED and NOT MATERIALIZED keywords.

The old advice “CTEs are an optimization fence” comes from PostgreSQL 11 and earlier, which always materialized them. If you read it in an old blog post, that is why.

Portability notes

• PostgreSQL, DuckDB, MySQL 8+, SQLite, BigQuery, Snowflake all support WITH and WITH RECURSIVE.
• SQL Server supports CTEs and recursion but does not use the word RECURSIVE: you write plain WITH. It also needs a semicolon before WITH if the previous statement did not end with one, which is why you see ;WITH in SQL Server code.
• Data-modifying CTEs (WITH deleted AS (DELETE ... RETURNING *) SELECT ...) are a PostgreSQL feature. DuckDB and most other databases allow only SELECT inside a CTE.
• The 'UUS'::VARCHAR cast in the anchor fixes the column type for both parts of the recursion. PostgreSQL insists on matching types between the anchor and the recursive part; CAST('UUS' AS VARCHAR) is the portable spelling.

Common mistakes

1. A comma after the last CTE. CTEs are separated by commas, but there is no comma between the last one and the main SELECT. ), SELECT ... is a syntax error.
2. Writing WITH twice. One statement has one WITH; extra CTEs follow after commas: WITH a AS (...), b AS (...).
3. Expecting a CTE to survive the statement. After the semicolon, the name is gone. If the next query needs it, repeat the CTE or create a temporary table.
4. Relying on ORDER BY inside a CTE. It does not fix the order of the final result. Sort in the main query.
5. A recursive CTE without a stop condition. Always cap the depth or make sure each round eventually returns no rows. UNION instead of UNION ALL helps when paths revisit the same rows.
6. A long chain nobody checks. Seven CTEs are readable only if each one is correct. Look at the row count of every step when you build the chain, especially after joins.

If you want the subquery forms that CTEs replace, see SQL subqueries. Many analytical CTE chains end in a window function; the window functions guide and the cohort retention guide show longer real chains.

FAQ

What is a CTE in SQL?

A common table expression: a named temporary result defined with WITH name AS (SELECT ...) and used by the query that follows. It exists only for that one statement.

What is the difference between a CTE and a subquery?

Logically, none: a CTE that is used once behaves like a derived table. The differences are practical. A CTE has a name, sits at the top so the query reads in order, can be referenced several times, and can be recursive.

Can I use multiple CTEs in one query?

Yes. Write WITH once and separate the CTEs with commas. Each CTE can use the ones defined before it.

Are CTEs slower than subqueries?

Not in current PostgreSQL or DuckDB: a CTE used once is inlined like a subquery. In PostgreSQL 11 and older, CTEs were always materialized, which could block optimizations. Use MATERIALIZED or NOT MATERIALIZED if you need to control it.

What is a recursive CTE used for?

Hierarchies and graphs: org charts, category trees, routes between airports, chains of events. It repeatedly applies a query to its own previous output until no new rows appear.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文