SQL Subqueries vs CTEs: When to Use Each (2026)

Quick Answer: What's the Actual Difference?

The Subquery Nobody Wanted to Touch

A query to find "customers who spent above the average order value" starts simple, as a single subquery computing that average inline. Then someone needs to exclude cancelled orders from the comparison. Then the same average needs to appear again, in a different condition, so it gets copy-pasted into a second nested subquery with a slightly different WHERE clause. Three weeks and a few more requirements later, the query is a subquery nested inside a subquery nested inside another subquery — and changing any one condition means hunting down every place the same logic was duplicated, hoping none of the copies drifted out of sync.

Nothing about that query is technically wrong — every subquery in it probably still returns the correct result. The real cost is that nobody can safely modify it anymore without re-reading the whole thing top to bottom. This is exactly the problem CTEs exist to solve: not different math, a different way of organizing the same logic.

What Is a Subquery

A subquery can appear in three places, each answering a different kind of question.

-- scalar subquery in SELECT: one value per row
SELECT order_id, order_total,
  (SELECT AVG(order_total) FROM orders) AS overall_avg
FROM orders;

-- subquery in WHERE: filter based on a computed value or set
SELECT order_id, order_total
FROM orders
WHERE order_total > (SELECT AVG(order_total) FROM orders);

-- subquery in FROM: a derived table, treated like any other table
SELECT region, avg_total
FROM (
  SELECT region, AVG(order_total) AS avg_total
  FROM orders
  GROUP BY region
) AS regional_averages
WHERE avg_total > 100;

All three are valid, standard SQL. The FROM version — a subquery acting as a "derived table" — is functionally close to a CTE, which is exactly why CTEs are often described as a cleaner syntax for that same pattern, rather than a fundamentally different capability.

Correlated vs Non-Correlated Subqueries

The distinction that actually affects performance: does the subquery reference a column from the outer query?

-- NON-CORRELATED: computed once, reused for every outer row
SELECT order_id, order_total
FROM orders
WHERE order_total > (SELECT AVG(order_total) FROM orders);

-- CORRELATED: references o.customer_id from the outer query —
-- conceptually re-evaluated once per outer row
SELECT o.order_id, o.order_total
FROM orders o
WHERE o.order_total > (
  SELECT AVG(o2.order_total)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

The second query answers a genuinely different question — "orders above that customer's own average" rather than the global average — and that per-customer precision is exactly why it needs to be correlated. But that precision has a cost: conceptually, the database re-runs the inner query once for every row the outer query considers, which can be significantly slower on a large table than a non-correlated subquery or an equivalent join, even though query planners often optimize the naive row-by-row execution into something smarter.

EXISTS vs IN

Both check membership, but they handle NULLs differently and often perform differently too.

-- customers who have placed at least one order — either works here
SELECT c.name
FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders);

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Beyond the NULL behavior, EXISTS often lets the query planner stop at the first match instead of building a complete list to compare against — usually the safer default for larger subqueries, while IN can read more naturally for a short, static, known list of values.

The WITH Clause: CTE Basics

A CTE names a query with WITH name AS (...), and the main query then references that name exactly like a real table.

WITH regional_averages AS (
  SELECT region, AVG(order_total) AS avg_total
  FROM orders
  GROUP BY region
)
SELECT region, avg_total
FROM regional_averages
WHERE avg_total > 100;

This is functionally identical to the FROM-subquery version shown earlier — same result, same execution in most cases. What changed is readability: regional_averages is now a name that documents what the intermediate result represents, instead of an anonymous block of SQL sitting inline inside FROM.

Chaining Multiple CTEs

A single WITH clause can define several CTEs, separated by commas, and each one can reference any CTE defined before it — this is exactly what fixes the "duplicated subquery" problem from this article's opening.

WITH non_cancelled AS (
  SELECT * FROM orders WHERE status != 'cancelled'
),
overall_avg AS (
  SELECT AVG(order_total) AS avg_total FROM non_cancelled
)
SELECT n.customer_id, n.order_total
FROM non_cancelled n, overall_avg a
WHERE n.order_total > a.avg_total;

The average is defined once, in overall_avg, and referenced wherever it's needed — no copy-pasted subquery to keep in sync if the definition of "cancelled" or the average calculation ever changes. Each named step is also independently testable: run SELECT * FROM non_cancelled on its own to sanity-check that one step before trusting the whole chain.

Recursive CTEs: Walking Hierarchical Data

A recursive CTE references itself, repeatedly building on its own previous result — the standard tool for organizational charts, category trees, or any parent-child hierarchy.

WITH RECURSIVE org_chart AS (
  -- anchor: the top-level employees with no manager
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive: each employee whose manager is already in org_chart
  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level;

The anchor query runs once, finding the starting rows (employees with no manager — the top of the hierarchy). The recursive query then runs repeatedly, each time joining against the CTE's own previous output, adding one more level of the hierarchy until a pass produces no new rows. It's the one thing a plain subquery genuinely cannot do — there's no way to express open-ended, self-referencing recursion without the WITH RECURSIVE syntax.

Are CTEs Faster? A Performance Note

Not automatically, and this is a common misconception worth correcting directly.

Decision Guide: Which Should You Use?

Mistakes That Give Away a Beginner

• Using NOT IN with a subquery that can return NULL — silently returns zero rows. Use NOT EXISTS instead.
• Copy-pasting the same subquery in multiple places instead of naming it once as a CTE — the exact trap from this article's opening.
• Assuming a CTE is automatically faster than the equivalent subquery. It depends on the database and version — test rather than assume.
• Writing a correlated subquery on a large table where a join or window function would express the same result more efficiently.
• Forgetting the RECURSIVE keyword when writing a self-referencing CTE — the syntax requires it explicitly, it's not inferred from the query's structure.

Practice: Write These Three Queries Yourself

Using the same orders table (order_id, customer_id, order_total, status), try writing these:

Frequently Asked Questions

Conclusion: Organize for the Reader, Not Just the Result

Subqueries and CTEs frequently produce identical results through different syntax — the real choice is about readability and reuse, not raw capability. Reach for a CTE the moment an intermediate result is needed more than once, or when a query has enough sequential steps that naming each one makes the whole thing easier to reason about. Keep a simple subquery for a genuinely one-off check. And treat NOT IN with suspicion the moment NULLs are possible — NOT EXISTS is almost always the safer choice.

Practice the three queries above against real tables — that closes out the intermediate tier: GROUP BY/HAVING, JOINs, aggregate functions, and subqueries/CTEs. The next article starts the advanced tier with window functions — aggregating without collapsing rows.