When Should You Use a CTE Instead of a Subquery?

A rewrite that looks safe and isn't

Here's a query that finds employees earning more than their own department's average salary, written as a correlated subquery:

Say you want to "clean this up" into a CTE, since that's the advice you'll read everywhere. The obvious move is to lift the inner SELECT into a WITH block and keep the correlation as-is:

WITH dept_avg AS (
  SELECT AVG(e2.salary) AS avg_salary
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
SELECT first_name, last_name, department_id, salary
FROM employees e
WHERE salary > (SELECT avg_salary FROM dept_avg);

This doesn't run. Every engine rejects it, DuckDB, Postgres, and SQL Server each phrase it differently, but they all mean the same thing: something like "e" isn't a known table there. The dept_avg CTE is parsed and bound as its own self-contained query, before FROM employees e further down even exists. A CTE has no line of sight into the query that uses it. That's the actual answer to "when do you use a CTE instead of a subquery": whenever the calculation genuinely needs to see a column from the current outer row, a CTE structurally cannot do that, so you're locked into a subquery. Once you don't need that, the two are interchangeable, and the real fix here is to stop correlating and aggregate once instead:

Same nine employees come back either way. The subquery recomputes an average freshly for every row it checks; the CTE computes all five department averages once, up front, then joins them back. Different shape, same answer, because nothing here actually depended on per-row correlation, it only looked like it did.

Two claims about CTEs that aren't quite right

Search "CTE vs subquery" and two claims show up in nearly every result: subqueries can be used with IN and EXISTS, CTEs can't; and CTEs are computed once no matter how many times you reference them, while subqueries rerun every time. Neither is accurate as a blanket rule.

A CTE is a named result set. Anywhere you can put (SELECT ...), you can put (SELECT ... FROM some_cte), IN included:

That's the CTE from the last section, filtered down to just the above-$74,000-average departments, then used inside an IN exactly like a plain subquery would be. There's no restriction to route around.

The "runs once" claim is subtler, and it's actually a fact about optimizers, not about the SQL language. PostgreSQL treated every CTE as an opaque, always-materialized step until version 12, which flipped the default so a CTE referenced exactly once gets inlined like a subquery, while one referenced more than once is still materialized by default unless you write NOT MATERIALIZED. DuckDB, which runs the playgrounds on this page, has changed its own default for exactly this decision between versions too. So "CTEs are computed once" was never a rule of SQL, it's an implementation detail that has changed inside single engines, more than once, within the last few years.

Where reuse actually pays off

The believable, durable reason to reach for a CTE isn't performance, it's that you can name an intermediate result and use it more than once without retyping the logic behind it. Take dept_avg again, this time referenced twice in the same query, once in a JOIN, once in a scalar subquery that compares departments to the average of the averages:

Only departments 1 and 5 clear the average-of-averages bar, so only their employees show up. Write this with subqueries instead and you either repeat the GROUP BY department_id, AVG(salary) aggregation twice, or nest one subquery inside another until it's hard to tell what's being compared to what. The CTE version names the intermediate step once and reuses it, which is the readability win people mean when they recommend CTEs, it just isn't a speed claim.

The one thing a subquery genuinely cannot do

Correlation is the reason you're sometimes forced into a subquery. Recursion is the mirror case: a query that needs to reference its own output, like walking an org chart through manager_id to find someone's full reporting chain, has no subquery equivalent at all. WITH RECURSIVE is the only way to express it in standard SQL. That deserves its own worked example rather than a rushed one here, so see the recursive CTE lesson for the anchor-plus-recursive-step pattern applied to this same employees table.

The actual decision

Skip the readability debate and ask two questions. Does this calculation need a column from the current outer row, one that's different for every row being checked? That's correlation, and it forces a subquery. Does it need to reference its own result to walk a hierarchy or a sequence? That's recursion, and only WITH RECURSIVE does it. If the answer to both is no, you're choosing between two spellings of the same query, and the only honest tiebreakers are how many times you're repeating the same logic (favor a CTE) and whether the query has to run on an engine or version where materialization behavior actually matters to you (check EXPLAIN, don't assume).

Where to go next

The CTE lesson and subqueries lesson cover the syntax of each on its own. The CTE vs subquery example is a shorter, syntax-focused version of the rewrite at the top of this post, and correlated subqueries goes deeper on the pattern this post opened with, using the movies dataset instead of employees. The practice problems are worth a visit if you want to try spotting which of your own queries actually need correlation before you reach for a WITH block out of habit.