Compare

CTE vs subquery vs temp table

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

When the intermediate result is reused across multiple statements (a stored procedure or interactive analysis), or when it's big enough that an index would help. Temp tables can be indexed; CTEs and subqueries can't. Trade off: physical write cost vs predictable reuse.

Short answer: All three name an intermediate result so you can use it later. CTEs (WITH ... AS) are scoped to one query and read top-to-bottom. Subqueries are inline expressions inside another query. Temp tables are persisted (for the session or transaction), can be indexed, and survive across multiple statements. Choose by how often you reuse the result and how big it is.

Quick comparison #

	CTE (`WITH`)	Subquery	Temp table
Lifetime	one statement	one statement	session or transaction
Reusable across statements	no	no	yes
Indexable	no	no	yes
Materialized?	engine-dependent (often inlined)	almost always inlined	always physical
Readability for multi-step logic	best	worst (nesting)	good
Cost of intermediate write	none	none	physical `INSERT`
Use for	naming and chaining steps in one query	one-off filter or scalar	reused result, big intermediate, or cross-statement workflow

The same logic, three ways #

Loading SQL editor...

When to use which #

Subquery — one-off, single-use, fits on a line or two. Scalar subqueries (SELECT (SELECT MAX(...) FROM ...)) and IN (SELECT ...) filters are textbook subquery cases. Past 2 levels of nesting, your code is harder to read than the equivalent CTE chain, so switch.

CTE (WITH) — the default for multi-step transformations inside one query. Each step gets a name; downstream steps reference it as if it were a table. The query reads top-to-bottom like a small script. Required for recursive queries (WITH RECURSIVE), which have no subquery equivalent.

Temp table: reach for it when the intermediate result is reused across multiple statements (a procedure or an interactive analysis), or when it's big enough that an index would help, or when you want the engine to compute it once and store it. Trades off the cost of a physical write for predictable reuse.

Permanent table or dbt model: the right call when the result is used by more than one workflow, by more than one person, or across more than one day. That is a different decision space: design it as a shared, governed data product rather than a one-query helper.

Performance and dialect notes #

CTE inlining: most modern engines (Postgres 12+, SQL Server, BigQuery, Snowflake, DuckDB, Redshift) inline non-recursive CTEs into the surrounding query, giving the same plan as a subquery. Older Postgres (≤11) materialized CTEs unconditionally, which sometimes hurt performance. Postgres 12+ added AS MATERIALIZED / AS NOT MATERIALIZED to override the planner's choice.
Subquery vs CTE plan equality: in modern engines, WHERE id IN (SELECT ...), JOIN (SELECT ...) ON ..., and the CTE rewrite usually produce identical plans. Choose for readability, not folklore.
Recursive CTEs: only CTEs can recurse (WITH RECURSIVE). Subqueries and temp tables can't. See Recursive CTE.
Temp tables: scope rules differ. Postgres / SQL Server / DuckDB scope to session by default; SQL Server's #temp is session, ##temp is global; MySQL temp tables are connection-scoped. BigQuery and Snowflake have session-scoped temp tables but they're rarer in warehouse workflows than in OLTP.

FAQ #

Is a CTE faster than a subquery?

On modern engines, no: they usually produce the same query plan. CTEs are inlined unless marked otherwise. The performance myth comes from older Postgres (≤11) which always materialized CTEs, which sometimes hurt performance. Choose by readability, not folklore.

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

When the intermediate result is reused across multiple statements (a stored procedure or interactive analysis), or when it's big enough that an index would help. Temp tables can be indexed; CTEs and subqueries can't. Trade off: physical write cost vs predictable reuse.

Can a CTE reference another CTE?

Yes — CTEs are read top-to-bottom; each can reference any earlier CTE in the same WITH list. Recursive CTEs (WITH RECURSIVE) reference themselves. You can't reference a later CTE.

What's a recursive CTE for?

Walking variable-depth structures: organizational hierarchies, category trees, graph traversals, generating sequences. Only CTEs can recurse; subqueries and temp tables can't express it. Format: WITH RECURSIVE cte AS (anchor UNION ALL recursive_step) SELECT ... FROM cte.

How long does a CTE live?

For the duration of the single statement that defines it. Once the query finishes, the CTE is gone. To reuse a result across statements, materialize it as a temp table or persistent table.

When does Postgres materialize a CTE?

Postgres 12+ inlines CTEs by default. To force materialization (the older default behavior), use WITH cte AS MATERIALIZED (...). To force inlining, AS NOT MATERIALIZED. Useful when the planner makes the wrong choice and you're measuring with EXPLAIN ANALYZE.

Tip

Default to CTEs for any query with more than one logical step. Three CTEs chained vertically (filter → join → aggregate) reads like a paragraph; the same logic written as nested subqueries reads like an onion. The performance difference on modern engines is usually nil. Reach for temp tables only when you've measured a real benefit, like a 10-minute query that drops to 30 seconds because an index on the intermediate makes the next join cheap.