Subqueries & CTEs

Some questions cannot be answered in a single pass. "Which books cost more than average?" needs the average first. "What does each customer spend per order, on average?" needs per-order totals before per-customer averages. Because every query returns a table, SQL lets you use one query's result inside another. This lesson covers subqueries in each position they can appear, the EXISTS operator, LATERAL, and common table expressions (CTEs) — including recursive ones that walk hierarchies.

#Scalar subqueries

A subquery in parentheses that returns exactly one row and one column can be used anywhere a single value can:

SELECT title, price
FROM books
WHERE price > (SELECT avg(price) FROM books)
ORDER BY price DESC;

           title           | price
---------------------------+-------
 Kafka on the Shore        | 18.50
 Norwegian Wood            | 17.00
 The Dispossessed          | 16.00
 The Left Hand of Darkness | 15.50
(4 rows)

The inner query runs once, produces 14.072, and the outer query compares against it. You could not write WHERE price > avg(price): aggregates are not allowed in WHERE, and even if they were, the outer query has no notion of "all books" at that point.

If a scalar subquery returns more than one row, the query fails at run time:

SELECT title FROM books
WHERE price = (SELECT price FROM books WHERE genre = 'fantasy');

ERROR:  more than one row returned by a subquery used as an expression

If it returns zero rows, its value is NULL. Both behaviours are good reasons to make sure a scalar subquery is guaranteed to return one row — typically by using an aggregate or filtering on a key.

#Correlated subqueries

A subquery can refer to columns of the outer query. It is then correlated: logically it is re-evaluated for each outer row. This one finds each customer's latest order date:

SELECT c.name,
       (SELECT max(o.ordered_on)
        FROM orders AS o
        WHERE o.customer_id = c.customer_id) AS last_order
FROM customers AS c
ORDER BY c.customer_id;

     name     | last_order
--------------+------------
 Ada Park     | 2025-03-15
 Ben Okafor   | 2025-03-30
 Chloe Martin | 2025-02-14
 Dev Raman    | 2025-03-22
 Elena Rossi  | 2025-03-08
 Farid Haddad | (null)
(6 rows)

Farid gets NULL because max over zero rows is NULL — the same result a LEFT JOIN plus GROUP BY would give. Correlated subqueries read naturally ("for each customer, look up…"). PostgreSQL rewrites IN and EXISTS subqueries into joins, but a correlated scalar subquery in the select list like this one usually runs as a SubPlan, executed once per outer row — fine with an index on orders.customer_id, slow without one on large tables.

#IN and EXISTS

#IN with a subquery

x IN (subquery) is true when x equals any value the subquery returns. Customers who have bought a Terry Pratchett book (author_id = 2):

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN books       AS b  ON b.book_id   = oi.book_id
  WHERE b.author_id = 2
)
ORDER BY name;

    name
------------
 Ada Park
 Ben Okafor
(2 rows)

Ada bought two Pratchett books and Ben three, yet each appears once. That is the key difference from a join: IN asks a yes/no question per outer row, so it never multiplies rows. This is called a semi-join.

#EXISTS

EXISTS (subquery) is true if the subquery returns at least one row. What the subquery selects is irrelevant, so by convention it selects 1:

SELECT c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
)
ORDER BY c.name;

    name
-------------
 Ada Park
 Ben Okafor
 Dev Raman
 Elena Rossi
(4 rows)

EXISTS is almost always correlated. It can stop at the first matching row, and it handles NULLs cleanly, which matters a great deal for the negative form.

#The NOT IN trap

Which customers have never referred anyone? The obvious query returns nothing:

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT referred_by FROM customers);

 name
------
(0 rows)

The subquery returns NULL, 1, NULL, 2, 4, 1. As you saw in the filtering lesson, x NOT IN (..., NULL) can never be true — for Chloe it evaluates 3 <> 1 AND 3 <> 2 AND 3 <> 4 AND 3 <> NULL, which is unknown. One NULL in the subquery's output silently empties the result. NOT EXISTS asks the question correctly:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM customers AS r
  WHERE r.referred_by = c.customer_id
)
ORDER BY c.name;

     name
--------------
 Chloe Martin
 Elena Rossi
 Farid Haddad
(3 rows)

#Subqueries in FROM

A subquery in FROM (a derived table) is a temporary table for the duration of the query. It must have an alias. This is how you aggregate in stages. To find each customer's average order value, you need order totals first — averaging line amounts directly would give the average line, not the average order:

SELECT c.name,
       count(*)               AS orders,
       round(avg(t.total), 2) AS avg_order_value
FROM (
  SELECT order_id, sum(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
) AS t
JOIN orders    AS o ON o.order_id    = t.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id
ORDER BY avg_order_value DESC;

    name     | orders | avg_order_value
-------------+--------+-----------------
 Elena Rossi |      1 |           38.97
 Dev Raman   |      2 |           36.13
 Ada Park    |      3 |           24.99
 Ben Okafor  |      3 |           24.49
(4 rows)

The inner query produces one row per order, so joining it to orders is one-to-one and nothing gets double counted. This is the fix for the fan-out problem from the aggregation lesson.

#LATERAL: a subquery per row

An ordinary derived table cannot refer to other tables in the same FROM. Adding LATERAL allows it, so the subquery runs once per outer row. That makes "top N per group" straightforward — here, each author's two most expensive books:

SELECT a.name, top.title, top.price
FROM authors AS a
CROSS JOIN LATERAL (
  SELECT b.title, b.price
  FROM books AS b
  WHERE b.author_id = a.author_id
  ORDER BY b.price DESC, b.title
  LIMIT 2
) AS top
ORDER BY a.name, top.price DESC;

       name        |           title           | price
-------------------+---------------------------+-------
 Haruki Murakami   | Kafka on the Shore        | 18.50
 Haruki Murakami   | Norwegian Wood            | 17.00
 Octavia E. Butler | Kindred                   | 14.00
 Octavia E. Butler | Parable of the Sower      | 13.25
 Terry Pratchett   | Going Postal              | 11.50
 Terry Pratchett   | Guards! Guards!           | 10.99
 Ursula K. Le Guin | The Dispossessed          | 16.00
 Ursula K. Le Guin | The Left Hand of Darkness | 15.50
(8 rows)

CROSS JOIN LATERAL drops authors whose subquery returns nothing (N. K. Jemisin). To keep them, write LEFT JOIN LATERAL (...) AS top ON true. The window functions lesson shows another way to solve top-N problems.

#Common table expressions

Nested subqueries become hard to read quickly. A common table expression (CTE) names a subquery up front with WITH, so the main query reads top to bottom like a sequence of steps:

WITH order_totals AS (
  SELECT order_id, sum(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
valid_orders AS (
  SELECT o.order_id, o.customer_id, t.total
  FROM orders AS o
  JOIN order_totals AS t USING (order_id)
  WHERE o.status <> 'cancelled'
)
SELECT c.name,
       count(*)     AS orders,
       sum(v.total) AS spent,
       max(v.total) AS biggest_order
FROM valid_orders AS v
JOIN customers AS c USING (customer_id)
GROUP BY c.customer_id
ORDER BY spent DESC;

    name     | orders | spent | biggest_order
-------------+--------+-------+---------------
 Ada Park    |      3 | 74.96 |         32.97
 Ben Okafor  |      3 | 73.48 |         33.48
 Dev Raman   |      2 | 72.25 |         41.25
 Elena Rossi |      1 | 38.97 |         38.97
(4 rows)

Each CTE can use the ones defined before it, and the main query can reference any of them, as many times as it likes. You can test a pipeline incrementally by running SELECT * FROM order_totals before writing the next step.

#Are CTEs slower?

Before PostgreSQL 12, every CTE was computed once and stored, acting as an "optimisation fence" that hid it from the planner. Since then, a non-recursive CTE that is referenced only once and has no side effects is inlined — planned exactly as if you had written a subquery — so readability costs nothing. You can override the choice: WITH t AS MATERIALIZED (...) forces it to be computed once (useful when it is expensive and referenced several times), and AS NOT MATERIALIZED forces inlining.

#Recursive CTEs

WITH RECURSIVE lets a CTE refer to itself, which is how SQL walks hierarchies of unknown depth: org charts, folder trees, bills of materials — or our referral chain. A recursive CTE has two parts joined by UNION ALL:

1. The anchor query produces the starting rows.
2. The recursive query joins the CTE to the table to produce the next level. It runs repeatedly, each time seeing only the rows produced by the previous iteration, until it returns nothing.

Who did Ada bring in, directly or indirectly?

WITH RECURSIVE referral_tree AS (
  -- anchor: start at Ada
  SELECT customer_id, name, 0 AS depth, name AS path
  FROM customers
  WHERE customer_id = 1

  UNION ALL

  -- recursive step: customers referred by anyone already in the tree
  SELECT c.customer_id, c.name, rt.depth + 1, rt.path || ' > ' || c.name
  FROM customers AS c
  JOIN referral_tree AS rt ON c.referred_by = rt.customer_id
)
SELECT depth, name, path
FROM referral_tree
ORDER BY path;

 depth |     name     |                      path
-------+--------------+-------------------------------------------------
     0 | Ada Park     | Ada Park
     1 | Ben Okafor   | Ada Park > Ben Okafor
     2 | Dev Raman    | Ada Park > Ben Okafor > Dev Raman
     3 | Elena Rossi  | Ada Park > Ben Okafor > Dev Raman > Elena Rossi
     1 | Farid Haddad | Ada Park > Farid Haddad
(5 rows)

Iteration 1 finds Ben and Farid (referred by Ada), iteration 2 finds Dev, iteration 3 finds Elena, and iteration 4 finds nobody, so recursion stops. The column types are fixed by the anchor, so expressions like the growing path must stay compatible with it.

#Practice

1. Using a correlated subquery, list the books priced above the average price of their own genre. Show title, genre and price.
2. List customers who have never bought a sci-fi book in a non-cancelled order (customers with no orders count). Use NOT EXISTS.
3. Using a CTE, compute the total of every non-cancelled order, then return only the orders whose total is above the average order total, largest first, with the customer's name.

SELECT b.title, b.genre, b.price
FROM books AS b
WHERE b.price > (
  SELECT avg(b2.price) FROM books AS b2 WHERE b2.genre = b.genre
)
ORDER BY b.genre, b.price DESC;

           title           |  genre   | price
---------------------------+----------+-------
 A Wizard of Earthsea      | fantasy  | 12.99
 Kafka on the Shore        | literary | 18.50
 The Dispossessed          | sci-fi   | 16.00
 The Left Hand of Darkness | sci-fi   | 15.50
(4 rows)

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN books       AS b  ON b.book_id   = oi.book_id
  WHERE o.customer_id = c.customer_id
    AND o.status <> 'cancelled'
    AND b.genre = 'sci-fi'
)
ORDER BY c.name;

     name
--------------
 Ben Okafor
 Chloe Martin
 Elena Rossi
 Farid Haddad
(4 rows)

WITH order_totals AS (
  SELECT o.order_id, c.name, sum(oi.quantity * oi.unit_price) AS total
  FROM orders AS o
  JOIN customers   AS c  ON c.customer_id = o.customer_id
  JOIN order_items AS oi ON oi.order_id   = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id, c.name
)
SELECT order_id, name, total
FROM order_totals
WHERE total > (SELECT avg(total) FROM order_totals)
ORDER BY total DESC;

 order_id |    name     | total
----------+-------------+-------
        5 | Dev Raman   | 41.25
        7 | Elena Rossi | 38.97
        6 | Ben Okafor  | 33.48
        1 | Ada Park    | 32.97
        9 | Dev Raman   | 31.00
(5 rows)

#Key takeaways

• A scalar subquery must return at most one row; it can appear anywhere a value can, and yields NULL when it returns nothing.
• Correlated subqueries refer to the outer row; IN and EXISTS test membership without multiplying rows.
• NOT IN (subquery) returns nothing if the subquery yields a NULL; use NOT EXISTS instead.
• Derived tables and CTEs let you aggregate in stages, which fixes join fan-out; LATERAL runs a subquery per outer row for top-N-per-group.
• Since PostgreSQL 12, single-use CTEs are inlined, so prefer them for readability; MATERIALIZED overrides when needed.
• WITH RECURSIVE walks hierarchies with an anchor plus a recursive step joined by UNION ALL; guard against cycles.