CTE vs Subquery vs Temp Table: Picking the Right SQL Tool

What and Why

You can express the same query in many ways. Three of the most common building blocks are subqueries, common table expressions (CTEs), and temporary tables. They look interchangeable in trivial cases, but they have different scopes, lifetimes, and performance characteristics. Choosing the right one keeps queries readable today and fast tomorrow.

Mental Model

subquery   : lives inside one statement, no name (or alias)
CTE        : named, lives inside one statement, may be inlined
temp table : named, lives for a session or transaction, real storage

A subquery is a query inside another query, in parentheses, used like a table or a value. A CTE is a named subquery declared with WITH and reusable inside the same statement. A temporary table is a real table that exists for the session (or transaction), can be indexed, and can be queried by multiple statements.

The further down the list you go, the more setup, but also the more reuse and tuning leverage.

Hands-on Example

Take an orders table with id, customer_id, total, created_at.

A subquery in the FROM clause:

SELECT customer_id, monthly_total
FROM (
  SELECT customer_id,
         date_trunc('month', created_at) AS m,
         SUM(total) AS monthly_total
  FROM orders
  GROUP BY customer_id, date_trunc('month', created_at)
) t
WHERE monthly_total > 1000;

The same query as a CTE:

WITH monthly AS (
  SELECT customer_id,
         date_trunc('month', created_at) AS m,
         SUM(total) AS monthly_total
  FROM orders
  GROUP BY customer_id, date_trunc('month', created_at)
)
SELECT customer_id, monthly_total
FROM monthly
WHERE monthly_total > 1000;

Functionally identical, but easier to read and trivially extended:

WITH monthly AS (...),
     ranked AS (
       SELECT *,
              RANK() OVER (PARTITION BY m ORDER BY monthly_total DESC) AS r
       FROM monthly
     )
SELECT * FROM ranked WHERE r <= 3;

A CTE shines when you refer to the same derived set more than once or when stacking steps. The same with subqueries forces you to repeat the inner SQL.

Now a temp table:

CREATE TEMP TABLE monthly AS
SELECT customer_id,
       date_trunc('month', created_at) AS m,
       SUM(total) AS monthly_total
FROM orders
GROUP BY customer_id, date_trunc('month', created_at);

CREATE INDEX ON monthly (m, monthly_total DESC);

SELECT * FROM monthly WHERE monthly_total > 1000;
SELECT * FROM monthly WHERE m = '2026-06-01';

The temp table is reusable across statements in your session and can carry indexes. Worth it when several queries hit the same intermediate result or when the intermediate is expensive to build and small to keep.

Recursive CTEs are a category of their own. Walk a tree of comments:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, body, 1 AS depth
  FROM comments
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.body, t.depth + 1
  FROM comments c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, id;

You cannot do this with a plain subquery; it needs the recursive structure.

Correlated subqueries reference the outer row:

SELECT c.id, c.name,
       (SELECT MAX(created_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

Convenient, but the inner query runs per outer row in many engines. A LEFT JOIN LATERAL or a window function is often faster.

Common Pitfalls

Assuming CTEs are always free. In older PostgreSQL (before 12), CTEs were always materialized: the optimizer could not push predicates into them. Modern Postgres inlines non-recursive CTEs by default, but you can force materialization with WITH foo AS MATERIALIZED (...). SQL Server and Oracle have similar nuances. Test the plan, do not trust folklore.

Using a temp table for a one-shot small derivation. The overhead of CREATE, write, and index can dwarf the query itself.

Writing massive nested subqueries because “I do not like CTEs”. Reviewers and your future self will pay the readability bill.

Forgetting that temp tables in some engines are per-session. If your connection pool reuses connections, a stale temp table may surprise you. Drop them explicitly or use ON COMMIT DROP.

Recursing without a termination condition or a depth limit. Bad data can produce cycles and exhaust memory. Add WHERE depth < 100 or detect cycles explicitly.

Practical Tips

Default to CTEs for multi-step queries. They read top-down like a script and are easy to test by selecting from each step.

Reach for a temp table when you will query the same intermediate three or more times, when you need an index on it, or when you want to inspect it interactively while debugging.

Use subqueries inline for simple lookups (WHERE id IN (SELECT ...)) where naming the set adds noise without value.

If a correlated subquery is slow, rewrite it. A JOIN plus aggregate or a window function nearly always wins on large tables.

Always look at the plan. The “right” choice depends on row counts, indexes, and engine. EXPLAIN ANALYZE settles arguments fast.

Wrap-up

Subqueries, CTEs, and temp tables are different points on a curve of scope, persistence, and ceremony. Subqueries are quick and local. CTEs name steps and make stacked logic readable. Temp tables persist and accept indexes. Pick the smallest tool that makes the query both clear and fast, and you will rarely write SQL you regret reading later.