SQL Subqueries vs CTE: How to Write Readable Queries

The short answer

• A subquery is a query inside another query: in WHERE, SELECT or FROM. Good when the logic fits on one line.
• A CTE (Common Table Expression, WITH) is a named intermediate result placed before the main query. Good when there are several steps to read in order.
• A recursive CTE (WITH RECURSIVE) handles trees and graphs: categories, org charts, referral chains.

The choice is mainly about readability. Performance is the same in most cases; the exceptions are covered below.

Kinds of subqueries

-- Scalar: a single value
SELECT name, price, price - (SELECT AVG(price) FROM products) AS diff
FROM products;

-- In a condition: EXISTS / IN
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Derived table in FROM
SELECT city, avg_total
FROM (SELECT city, AVG(total) AS avg_total FROM orders GROUP BY city) t
WHERE avg_total > 500;

A correlated subquery references the outer query (like o.customer_id = c.id above) and logically runs once per row. The planner often rewrites it as a join, but not always.

The same report with a CTE

Task: customers whose spending this year is above the average across all customers.

With nested subqueries:

SELECT customer_id, spent
FROM (SELECT customer_id, SUM(total) AS spent
      FROM orders WHERE created_at >= '2026-01-01'
      GROUP BY customer_id) s
WHERE spent > (SELECT AVG(spent) FROM
      (SELECT SUM(total) AS spent FROM orders
       WHERE created_at >= '2026-01-01' GROUP BY customer_id) x);

With a CTE:

WITH spending AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  WHERE created_at >= '2026-01-01'
  GROUP BY customer_id
),
avg_spending AS (
  SELECT AVG(spent) AS avg_spent FROM spending
)
SELECT s.customer_id, s.spent
FROM spending s, avg_spending a
WHERE s.spent > a.avg_spent;

The logic is written once, the steps have names, and the query reads top to bottom. You can debug each CTE separately by running it as a plain SELECT.

Recursive CTE: hierarchies

Table categories(id, parent_id, name). Get "Electronics" with all nested subcategories and their depth:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 10                      -- root
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id  -- children of rows found so far
  WHERE t.depth < 20                 -- guard against cycles
)
SELECT * FROM tree ORDER BY depth, name;

How it works: the first part (the anchor) finds the root; the second (the recursive part) adds the children of rows already found, step by step, until no new rows appear. The same pattern builds an "employee, manager, manager's manager" chain.

If the data can contain a cycle (A points to B, B points to A), the query never ends without a depth limit. MySQL also enforces a cte_max_recursion_depth limit.

Performance

• In PostgreSQL 12+, a plain CTE referenced once is inlined into the main query and optimized like a subquery. You can control this explicitly with WITH x AS MATERIALIZED (...) or NOT MATERIALIZED.
• Older PostgreSQL versions always materialized CTEs, which could stop outer conditions from using indexes.
• Materialization helps when a heavy CTE is referenced several times.
• NOT IN with a subquery is risky: if the subquery returns even one NULL, the result is empty. Use NOT EXISTS.
• A correlated subquery in SELECT over a large table may run row by row — check the plan with EXPLAIN.

How to choose