Subqueries and CTEs in SQL

A subquery is a query inside another query. CTEs (WITH clauses) are named, top-level subqueries that read top to bottom. Both let you build big queries out of small, comprehensible pieces — and getting good at them is the single biggest readability upgrade SQL offers.

The running dataset

Still the users, orders, products schema.

-- users(id, name, country, age)
-- products(id, name, price)
-- orders(id, user_id, product_id, quantity, total, status)

The three shapes of subquery

A subquery returns one of three shapes:

Shape	Example	Where it goes
Scalar	One row, one column	Anywhere a value goes
Row	One row, many columns	Rare; some WHERE clauses
Table	Many rows, many columns	FROM, IN, EXISTS

Scalar subquery

A scalar subquery yields a single value. You can use it like any literal.

-- Orders worth more than the average order
SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

The inner query returns one number; the outer query compares each total to it.

You can also project a scalar subquery in the SELECT list:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

This is fine for small lookups; for larger workloads a LEFT JOIN with GROUP BY is usually faster.

Table subquery

A table subquery returns many rows. Wrap it in parentheses and treat it like a table:

SELECT country, COUNT(*) AS big_orders
FROM (
  SELECT u.country, o.id
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.total > 50
) AS big
GROUP BY country;

The inner query produces a virtual table called big. The outer query groups it. This pattern is common; CTEs (below) make it more readable.

IN with a subquery

IN is the easiest way to check membership against a set the inner query returns.

-- Users who have at least one paid order
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

The inner query returns a list of user_ids; the outer query keeps users whose id appears in that list.

NOT IN does the opposite, with one important wrinkle: if the inner query produces any NULLs, NOT IN returns no rows. This is technically correct (NULL means “unknown” so the database can’t prove anything is not in the set) but it surprises everyone. Use NOT EXISTS instead when the inner column might be nullable.

EXISTS and NOT EXISTS

EXISTS returns true if the subquery has at least one row.

-- Users with any order
SELECT name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Users with no orders
SELECT name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

A few notes:

• The SELECT 1 is conventional. EXISTS only cares whether a row comes back, not what’s in it.
• The inner query references the outer query (o.user_id = u.id). This is a correlated subquery — it runs once per row of the outer query.
• NOT EXISTS handles NULLs the way you’d expect (unlike NOT IN).

The “find users with no orders” pattern is also writable with LEFT JOIN ... WHERE o.id IS NULL — see SQL JOINs. Both are fine; pick whichever reads more clearly.

ANY and ALL

ANY (alias SOME) and ALL compare a single value against every value returned by a subquery.

-- Products priced higher than ANY paid order's total (i.e. higher than at least one)
SELECT name, price
FROM products
WHERE price > ANY (SELECT total FROM orders WHERE status = 'paid');

-- Products priced higher than ALL paid orders' totals (i.e. higher than every one)
SELECT name, price
FROM products
WHERE price > ALL (SELECT total FROM orders WHERE status = 'paid');

In practice MAX and MIN cover most cases more readably:

WHERE price > (SELECT MAX(total) FROM orders WHERE status = 'paid')

Reach for ANY/ALL when the comparison is awkward to express otherwise.

Correlated subqueries

A correlated subquery refers to a column from the outer query. It runs once per outer row.

-- Each user with the total of their largest order
SELECT u.name,
       (SELECT MAX(o.total) FROM orders o WHERE o.user_id = u.id) AS max_order
FROM users u;

Correlated subqueries are powerful and sometimes the most natural expression. The catch: each outer row triggers another execution of the inner query, so on large data they can be slow. For repeated patterns like “the largest X per Y”, a GROUP BY or window function is usually faster.

WITH clauses (CTEs)

A Common Table Expression — a CTE — is a named subquery that you write before the main query.

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, SUM(total) AS spent
FROM paid_orders
GROUP BY user_id;

paid_orders is the name; the part inside parentheses is the query. The main query treats it like a table. You can chain CTEs and have each one build on the previous:

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
),
user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM paid_orders
  GROUP BY user_id
)
SELECT u.name, ut.spent
FROM users u
JOIN user_totals ut ON ut.user_id = u.id
ORDER BY ut.spent DESC;

Read it top to bottom. First narrow to paid orders. Then total per user. Then join to the user list. Each step has a name. Each step can be commented. Each step is easy to test on its own (just paste it into a SELECT * and run it).

The same query as a nested subquery:

SELECT u.name, ut.spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS spent
  FROM (SELECT * FROM orders WHERE status = 'paid') AS po
  GROUP BY user_id
) ut ON ut.user_id = u.id
ORDER BY ut.spent DESC;

Functionally identical. Readability-wise, the CTE version wins almost every time.

When CTE beats subquery

Lean on a CTE when:

• The subquery is referenced more than once.
• The intermediate result has a meaningful name.
• The query is at risk of growing past five or six lines of nesting.

Lean on a plain subquery when:

• The inner query is trivially short (a single value).
• It’s used exactly once.
• The reader expects to see it inline.

Recursive CTEs

A recursive CTE references itself, letting you walk hierarchies. The classic example is an organisational tree:

WITH RECURSIVE org_tree AS (
  -- Anchor: the top-level employee
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: anyone whose manager is already in the result
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

You won’t reach for recursive CTEs often, but when the data is a tree or a graph they’re sometimes the only clean solution. Numbered series, chained references, and bill-of-materials explosions all fit this shape.

A safety note: every recursive CTE needs a terminating condition. If your anchor and recursive parts never stop producing new rows, the query runs forever — most databases impose a recursion-depth limit that will eventually kill it.

CTEs and CRUD

Most databases let you use CTEs with UPDATE, INSERT, and DELETE, not just SELECT. A common pattern is “update by computed key”:

WITH refunds AS (
  SELECT id FROM orders WHERE status = 'refunded' AND total > 100
)
UPDATE orders
SET status = 'reviewed'
WHERE id IN (SELECT id FROM refunds);

Postgres takes this further with WITH ... RETURNING, letting you chain mutations and reads. Useful but dialect-specific — check the docs for your database.

A worked example

A “loyal customer” report — users with at least 2 paid orders and total spend above the median spend.

WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
),
per_user AS (
  SELECT user_id, COUNT(*) AS paid_orders, SUM(total) AS spent
  FROM paid
  GROUP BY user_id
),
median AS (
  -- Approximation: average spend across the per_user table
  SELECT AVG(spent) AS threshold FROM per_user
)
SELECT u.name, pu.paid_orders, pu.spent
FROM per_user pu
JOIN users u ON u.id = pu.user_id
CROSS JOIN median m
WHERE pu.paid_orders >= 2
  AND pu.spent > m.threshold
ORDER BY pu.spent DESC;

Four named steps, each one a one-liner you could test in isolation. The CTE structure is the documentation.

Common beginner mistakes

• Using NOT IN with a subquery that returns NULL and being mystified by an empty result.
• Forgetting that a correlated subquery runs per outer row and writing one over a million-row table.
• Nesting subqueries five levels deep instead of breaking them into named CTEs.
• Re-running the same expensive subquery twice in one query instead of putting it in a CTE.
• Recursive CTEs without a clear stopping condition.

Recap

You now know:

• A scalar subquery returns one value and can sit where a value goes
• A table subquery returns rows and can sit where a table goes
• IN, EXISTS, ANY, ALL connect outer queries to inner result sets
• Correlated subqueries reference the outer row; they’re powerful but row-by-row
• WITH name AS (...) defines a CTE; you can chain several
• WITH RECURSIVE walks hierarchies
• A CTE almost always beats a deeply nested subquery for readability

These are the composition tools. With them, even an intimidating report is just a stack of small, named queries.

Next steps

You can now write almost anything. The next question is whether the database can run it quickly. That means indexes, query plans, and a feel for what’s cheap and what isn’t.

Next: SQL Indexes and Query Performance Basics

Related reading: SQL JOINs, SQL Aggregations and GROUP BY.

Questions or feedback? Email codeloomdevv@gmail.com.