CTE vs Subquery vs Temp Table: Readability, Reuse and Materialization

CTE vs subquery vs temp table in one line: a subquery is an intermediate result written inline, used once, at the point where it is needed; a CTE is the same result given a name at the top of one statement, so it can be read first and referenced several times; a temporary table is that result written out for the rest of the session, where it can be indexed, analysed and reused across statements, at the cost of a write. All three can return identical rows, as the code below shows. They differ in scope, in how many times you can refer to the result, and in whether the engine computes it once or folds it back into the outer query. The PostgreSQL behaviour quoted here is from its manual, as of October 2026; the SQLite behaviour is run live.

Form	Lives for	Can be referenced
Subquery	one place in one statement	once, where it is written
CTE (WITH)	one statement	many times, by name
Temp table	the session	many statements, with indexes

The problem it solves

Almost every real query has an intermediate step: the average per group, the latest row per customer, the set of ids that passed a filter. You need that step's result somewhere else in the query, perhaps in two places. SQL gives you three ways to hold it, and the wrong one costs you either readability, when the subquery is nested four deep, or time, when a temp table is written for a result that was needed once.

Recursion, the one job only a CTE can do, is covered in SQL recursive CTE explained. This article is about the ordinary, non-recursive choice.

The intuition

Think of a calculation on paper.

• A subquery is the working written in the margin, right next to the line that uses it. Fine for one small step. Nest three of them and the page reads inside out: the innermost bracket runs first, but you read it last.
• A CTE is a named line at the top: "let avg_flow be the average per gate". The main query then reads top to bottom, and avg_flow can appear twice without being written twice. It is scoped to the one statement; the next statement has never heard of it.
• A temp table is a scratch sheet you keep for the day. You write the result down once, and every later query reads it. Because it is a real table, you can put an index on it and the planner can gather statistics about it. It disappears when the connection closes, and other connections never see it.

Then materialization, which is where the choice affects speed. A CTE referenced once is usually just a subquery with a name: the planner inlines it into the outer query and optimizes the whole thing together. The PostgreSQL manual states that since version 12 a non-recursive CTE referenced exactly once is folded into the parent query unless you write MATERIALIZED, and that a CTE referenced more than once is materialized by default, computed once and read from a temporary result. Before version 12, every CTE was an optimization fence. SQLite says a CTE may be implemented as a subquery or materialized at the planner's discretion, and offers the same MATERIALIZED and NOT MATERIALIZED hints. A temp table is always materialized: you asked for it to be written.

So the decision is:

1. Used once, small: subquery.
2. Used once, but the statement is hard to read: CTE, and the planner will usually inline it anyway.
3. Used several times in one statement: CTE, computed once.
4. Used across several statements, or large enough to need an index: temp table.

Watch it run

The animation uses five gates, each naming the one it feeds from. A recursive CTE starts with an anchor, the spring, and the recursive arm joins the table back onto what the previous pass produced, one hop at a time, until a pass finds nothing. That is the one shape the other two forms cannot write: a subquery cannot refer to itself, and a temp table would need a loop in application code to fill. For the non-recursive case below, the three forms are interchangeable in what they return, and the question becomes which one the query is easier to read and run with.

The code

The lesson's gate table plus two days of flow readings. The question: which gates average more than 250 litres? The average per gate is the intermediate result, written three ways:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE gate (name TEXT, feeds TEXT);
  INSERT INTO gate VALUES ('spring',NULL),('mill','spring'),
    ('town','mill'),('harbour','town'),('quarry',NULL);
  CREATE TABLE flow (gate TEXT, day TEXT, litres INT);
  INSERT INTO flow VALUES
    ('spring','03-04',900),('spring','03-05',950),
    ('mill','03-04',600),('mill','03-05',640),
    ('town','03-04',300),('town','03-05',310),
    ('harbour','03-04',200),('harbour','03-05',180),
    ('quarry','03-04',50),('quarry','03-05',40);
""")

# 1. subquery: the average is written inline, where it is compared
sub = db.execute("""
  SELECT name FROM gate
  WHERE (SELECT AVG(litres) FROM flow WHERE flow.gate = gate.name) > 250
  ORDER BY name""").fetchall()

# 2. CTE: the same average, named at the top, joined below
cte = db.execute("""
  WITH avg_flow AS (
    SELECT gate, AVG(litres) AS litres FROM flow GROUP BY gate)
  SELECT g.name FROM gate g JOIN avg_flow a ON a.gate = g.name
  WHERE a.litres > 250 ORDER BY g.name""").fetchall()

# 3. temp table: the average is written out for the rest of the session
db.executescript("""
  CREATE TEMP TABLE avg_flow AS
    SELECT gate, AVG(litres) AS litres FROM flow GROUP BY gate;
""")
tmp = db.execute("""
  SELECT g.name FROM gate g JOIN avg_flow a ON a.gate = g.name
  WHERE a.litres > 250 ORDER BY g.name""").fetchall()

print(sub)
print(cte)
print(tmp, sub == cte == tmp)
# [('mill',), ('spring',), ('town',)]
# [('mill',), ('spring',), ('town',)]
# [('mill',), ('spring',), ('town',)] True

Three identical answers. The subquery version is correlated, so it computes one gate's average per outer row; the CTE and the temp table compute all five averages once and join.

Now the case the CTE is built for: the same intermediate result used twice in one statement, for each gate's average and for the farm-wide mean it is compared against. Then the scope rule, and what the planner did with the CTE:

db.execute("DROP TABLE avg_flow")                 # back to no temp table

print(db.execute("""
  WITH avg_flow AS (
    SELECT gate, AVG(litres) AS litres FROM flow GROUP BY gate)
  SELECT gate, litres,
         ROUND(litres - (SELECT AVG(litres) FROM avg_flow), 1) AS vs_mean
  FROM avg_flow ORDER BY litres DESC""").fetchall())
# [('spring', 925.0, 508.0), ('mill', 620.0, 203.0), ('town', 305.0, -112.0), ('harbour', 190.0, -227.0), ('quarry', 45.0, -372.0)]

try:
    db.execute("SELECT * FROM avg_flow")           # the CTE ended with its statement
except sqlite3.OperationalError as e:
    print(e)
# no such table: avg_flow

print([r[3] for r in db.execute("""EXPLAIN QUERY PLAN
  WITH a AS (SELECT gate, AVG(litres) AS litres FROM flow GROUP BY gate)
  SELECT litres FROM a WHERE gate = 'mill'""")])
# ['CO-ROUTINE a', 'SCAN flow', 'SCAN a']

avg_flow was referenced twice and the mean came out right; one statement later the name is gone. The plan for a once-referenced CTE shows SQLite running it as a co-routine that streams rows into the outer query, not as a stored result: there is no index to use, so WHERE gate = 'mill' is a scan over the five averages.

A temp table changes that. It can carry an index, and it is there for the next statement too:

db.executescript("""
  CREATE TEMP TABLE avg_flow AS
    SELECT gate, AVG(litres) AS litres FROM flow GROUP BY gate;
  CREATE INDEX temp.avg_flow_gate ON avg_flow(gate);
""")
print(db.execute("SELECT litres FROM avg_flow WHERE gate = 'mill'").fetchall())
# [(620.0,)]
print(db.execute("EXPLAIN QUERY PLAN SELECT litres FROM avg_flow WHERE gate = 'mill'").fetchone()[3])
# SEARCH avg_flow USING INDEX avg_flow_gate (gate=?)

The same lookup is now a SEARCH through an index. On five rows that is irrelevant; on five million intermediate rows read by a dozen later queries, it is the reason the temp table exists.

The complexity

The intermediate result costs the same to compute once, whatever you call it. What differs is how many times it is computed and what it costs to keep:

• Subquery: computed where it stands. A correlated one runs once per outer row, so the cost is O(outer rows × inner cost) unless the planner rewrites it.
• CTE: computed once per statement when materialized, and read from the temporary result for each reference; when inlined, it is optimized as part of the outer query, which can be faster because predicates can be pushed into it.
• Temp table: computed once per session, plus the write, plus index maintenance; every later read pays only the read.

Where it goes wrong

• A CTE as a performance fence by assumption. In PostgreSQL 12 and later a once-referenced CTE is inlined, so WITH does not force a result to be computed once. Write MATERIALIZED if that is what you need.
• The opposite: a CTE referenced twice, inlined by hand. Copying the subquery into both places computes it twice. Name it.
• A temp table for a one-off. You paid a write and the planner lost the chance to push your filters into the intermediate step.
• Expecting a temp table to be visible from another connection. It is session-local; connection pools hand you a different session than you think.
• Leaving a temp table behind in a long-lived connection. It stays until the session ends, holding space and stale data.

When it shows up in interviews

Usually as "how would you make this query readable?", with a four-level nested subquery on the whiteboard, or as "this CTE is slow, why?", where the answer is materialization and the version of the engine. The recursive follow-up, "now walk the hierarchy", is the one job only a CTE does, and it is covered in SQL recursive CTE explained. Reading what the planner did with the intermediate result is in how to read a query plan.

How to say it in an interview

"A subquery is an inline intermediate result used once. A CTE is the same result named at the top of the statement, so the query reads top to bottom and the name can be used several times; the planner inlines it when it is referenced once and materializes it when referenced more than once, in PostgreSQL 12 and later. A temp table writes the result out for the whole session, so I can index it and reuse it across statements. I reach for a subquery when it is small, a CTE when it is used twice or the query needs to be readable, and a temp table when several statements need it or it needs an index."

The naming-and-building-on-it idea is the CTEs and Recursion lesson.

Frequently asked questions

Is a CTE faster than a subquery?

Usually neither is faster: a CTE referenced once is inlined by PostgreSQL 12 and later and by SQLite's planner, so it becomes the same query as the subquery. A CTE referenced several times is computed once and reused, which beats repeating the subquery. Choose the CTE for readability and reuse, not for speed.

When should I use a temp table instead of a CTE?

When the intermediate result is needed by more than one statement, when it is large enough that an index on it pays off, or when the planner needs statistics about it to choose a good plan. A CTE lives for one statement and cannot be indexed; a temp table lives for the session and can.

Does a CTE get materialized?

It depends on the engine and on how many times it is referenced. PostgreSQL 12 and later materializes a non-recursive CTE referenced more than once and inlines one referenced once, unless you write MATERIALIZED or NOT MATERIALIZED. SQLite leaves the choice to its planner and accepts the same two hints. A temp table is always materialized.

Sources

• WITH Queries (Common Table Expressions) — PostgreSQL Documentation
• CREATE TABLE, temporary tables — PostgreSQL Documentation
• The WITH Clause — SQLite Documentation
• WITH (Common Table Expressions) — MySQL 8.4 Reference Manual