CTEs vs Subqueries – When to Use What

The one-sentence difference

A subquery is a query nested inside another query, used right where it's written. A CTE is a query given a name up front with WITH, then referenced later in the main query like it was a real table. They can express the same logic — the difference is almost entirely about structure and readability, not capability.

Same query, two ways

Say we want customers whose total spending is above the average customer's total spending. Here's the orders table:

The subquery version nests a subquery inside a subquery just to get one average value:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (
  SELECT AVG(t.total) FROM (
    SELECT SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
  ) t
);

The CTE version breaks the same logic into two named, readable steps:

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
avg_spent AS (
  SELECT AVG(total_spent) AS avg_total FROM customer_totals
)
SELECT customer_totals.*
FROM customer_totals, avg_spent
WHERE customer_totals.total_spent > avg_spent.avg_total;

Both return the same rows — customer_id 103, with $350 total, well above the $210 average. Same answer, very different reading experience.

Why the CTE version reads more easily

The subquery version forces you to read from the inside out: first find the innermost subquery, understand what it does, then work outward. The CTE version reads top to bottom, in the order the logic actually happens: first compute totals, then compute the average, then filter. Each step has a name that documents what it's for — customer_totals and avg_spent are self-explanatory in a way that a nested subquery never is.

Referencing a CTE more than once

This is the advantage a subquery genuinely can't match: a CTE can be referenced multiple times in the same query without repeating its logic. Here, customer_totals is used twice — once directly, once inside a second calculation — with zero duplication:

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.total_spent, m.max_total
FROM customer_totals c
CROSS JOIN (SELECT MAX(total_spent) AS max_total FROM customer_totals) m;

Doing this with subqueries alone would mean writing the exact same GROUP BY logic twice, in two different places — a maintenance risk if the logic ever needs to change.

When a subquery is still the right call

CTEs aren't automatically "better" — for small, single-use checks, an inline subquery is often the more direct choice:

SELECT * FROM employees
WHERE department_id = (
  SELECT department_id FROM departments WHERE name = 'Engineering'
);

Wrapping this single scalar lookup in a named CTE wouldn't make it clearer — it would just add ceremony. Correlated subqueries — the kind that reference a value from the outer query and re-run per row — are also usually written as subqueries, since each row genuinely needs its own nested lookup rather than one shared, precomputed step.

Does performance actually differ?

Usually not. Most modern databases optimize a non-recursive CTE the same way they'd optimize an equivalent subquery — the query planner can inline it, push predicates into it, or otherwise treat it just like a derived table. Historically, some databases (older PostgreSQL versions in particular) always materialized CTEs as an "optimization fence," which could occasionally hurt performance on large datasets. Modern PostgreSQL (12+) inlines CTEs by default now, and lets you force the old behavior with MATERIALIZED if you actually want it.

The one thing only a CTE can do

There's one capability where the comparison isn't close at all: recursion. A recursive CTE can reference itself to walk through hierarchical data — an employee's chain of managers, a category tree, a bill of materials — one level at a time. A subquery has no equivalent; it simply cannot do this. If you ever need to traverse a hierarchy of unknown depth, a CTE isn't just the more readable option, it's the only option.

Common mistakes

• Assuming CTEs are always faster. They aren't inherently — see the performance section above.
• Treating a CTE like a persisted temp table. A CTE only exists for the query it's defined in, and most databases re-evaluate it each time it's referenced rather than caching the result.
• Reaching for a CTE for a single, tiny lookup. Sometimes a plain subquery really is the clearer choice — don't add structure the query doesn't need.
• Nesting subqueries three levels deep instead of naming each step. If you can't describe what the innermost subquery does in five words, it probably wants to be a named CTE.