CTE vs Subquery vs Temp Table: When to Use Which

Quick comparison

CTE: readable, scoped to one statement

A CTE (WITH name AS (...)) gives an intermediate result a name and lets a query be read top-to-bottom as a sequence of named steps, instead of as deeply nested parentheses:

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month, revenue
FROM monthly_revenue
WHERE revenue > 10000;

It exists only for the duration of this one statement — it can't be queried again afterward, and no other session can see it. For a deeper dive into CTEs versus plain subqueries specifically, see CTEs vs Subqueries – When to Use What.

Subquery: nested, sometimes correlated

A subquery is a query nested directly inside another, with no independent name — it exists only in the exact spot it's written:

-- Non-correlated: computed once
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Correlated: re-evaluated per outer row
SELECT o.*
FROM orders o
WHERE o.amount > (
  SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id
);

The correlated version is something neither a CTE nor a temp table does the same way — it genuinely re-runs, conceptually, once per row of the outer query, referencing that specific row's values each time. See Correlated Subqueries Explained Simply for the full mechanics.

Temp table: persisted, indexable, reusable

A temp table is a genuine physical table — it has real storage, can have its own index, and survives across multiple separate statements within the same session or transaction:

CREATE TEMP TABLE monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

CREATE INDEX idx_month ON monthly_revenue(month);

-- Now reusable across as many separate queries as needed, in this session
SELECT * FROM monthly_revenue WHERE revenue > 10000;
SELECT AVG(revenue) FROM monthly_revenue;

This is the one option of the three that makes sense when a report runs several genuinely separate queries against the same expensive intermediate result — compute it once, index it, then query it repeatedly.

Materialization: the performance detail that trips people up

Whether a CTE's result is actually computed once and stored ("materialized"), or simply inlined into the surrounding query's plan like a subquery, depends on the database and, in some cases, on how the CTE is used:

The practical implication: a CTE referenced multiple times in the same query is not guaranteed to be computed only once — on some databases, each reference may recompute it from scratch. A temp table never has this ambiguity, since it's computed exactly once and then read from directly.

Decision guide

Common mistakes

• Assuming a CTE is always materialized once. On several databases it may be recomputed on every reference — a temp table is the only one of the three that guarantees single computation.
• Reaching for a temp table for a single, simple statement. The overhead of creating and dropping a physical table isn't worth it if a CTE or subquery would do the same job within one query.
• Writing a correlated subquery when a plain JOIN would do the same job faster. Correlated subqueries can be expensive if the database doesn't optimize them well; a rewrite as a join is often worth trying.
• Forgetting temp tables persist for the whole session — leftover temp tables from an earlier step can silently affect a later, unrelated query if names collide.