SQL CTEs vs Subqueries: When to Use Which

A common table expression (CTE), defined with WITH, and a subquery, nested directly inside another query, both let you compute an intermediate result and use it in a larger query — the difference is mostly about structure and readability, though it can occasionally affect how the query optimizer treats the query too.

What each one looks like

A subquery is a query nested inside another query’s clause — in the FROM, in a WHERE condition, or as a scalar expression in the SELECT list:

SELECT name, total
FROM (
  SELECT customer_id AS name, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE total > 1000;

A CTE pulls that same nested query out into a named block before the main query, using WITH:

WITH customer_totals AS (
  SELECT customer_id AS name, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT name, total
FROM customer_totals
WHERE total > 1000;

These two produce identical results. The difference so far is purely cosmetic — the CTE version names the intermediate step and reads top-to-bottom, while the subquery version nests it inline.

Where the difference actually matters

Readability compounds with complexity. A single-level subquery is easy to follow either way, but queries that combine several intermediate steps become hard to read as nested subqueries — each level of nesting pushes the next SELECT further to the right and further from the columns it operates on. CTEs flatten that structure: each step gets a name and sits at the top level, read in the order it’s computed, which is the main reason style guides tend to prefer CTEs once a query needs more than one derived table.

Reuse within a single query. A CTE can be referenced more than once in the same query — join it to itself, or reference it from two different branches of the main query — without repeating its definition. A subquery used the same way either has to be duplicated verbatim in both places, or the database has to be restructured to avoid the repetition entirely. This matters most in reporting queries that need the same aggregate compared against several different slices of data.

Recursive queries need a CTE. WITH RECURSIVE is how SQL expresses queries that reference their own output — walking a tree, traversing a graph of relationships, generating a sequence of dates. There’s no subquery equivalent; recursion is a CTE-only capability, and it’s one of the strongest reasons to reach for WITH regardless of any other consideration.

Optimizer behavior can differ, but it’s engine- and version-specific. Historically, some databases treated a CTE as an “optimization fence” — materializing its result once rather than folding it into the surrounding query’s plan the way a subquery might be. That could help (computing an expensive aggregate exactly once) or hurt (preventing the optimizer from pushing a filter down into the CTE to avoid scanning rows that get discarded anyway). Modern versions of most major databases have narrowed or removed this distinction, inlining simple CTEs much like subqueries in many cases — which means the only reliable way to know the actual behavior for your query, on your database version, is to check the plan yourself with EXPLAIN ANALYZE rather than assuming based on older documentation or folk wisdom.

CTE vs subquery

	CTE (WITH)	Subquery
Named, referenceable	Yes	No — must be repeated to reuse
Readability at scale	Better — flattens nested structure	Degrades with more nesting levels
Recursion	Supported (WITH RECURSIVE)	Not supported
Reuse in the same query	Yes, without duplication	Requires duplicating the subquery
Optimizer treatment	Historically sometimes materialized; increasingly inlined on modern engines	Generally inlined into the surrounding plan
Best fit	Multi-step queries, recursion, reused intermediate results	Simple, single-use nested filters or lookups

A practical rule of thumb

For a short, single-use filter — “orders where the amount exceeds this customer’s average” — a subquery is often the more direct choice: no need to name a one-off intermediate result you’ll never reference again. Once a query needs more than one derived step, needs the same intermediate result more than once, or needs recursion, a CTE almost always reads better and is easier for the next person (including future you) to follow. Neither choice replaces understanding how joins and GROUP BY/HAVING work — CTEs and subqueries are just two ways to structure queries that still rely on those fundamentals underneath, and for a result that needs to persist and be indexed rather than recomputed on every query, a materialized view is the better tool than either.

The takeaway

CTEs and subqueries frequently produce the same query plan and the same result — the real difference is structural: CTEs name intermediate steps, support reuse within a query, and are the only way to write recursive SQL, while subqueries stay more compact for a single, unrepeated nested filter. Default to a subquery for something simple and single-use; reach for a CTE once a query needs more than one step, needs to reuse a result, or needs recursion — and verify performance-sensitive assumptions with an actual query plan rather than outdated rules of thumb about how your specific database treats each one.