# Subqueries & CTEs > Nest queries inside queries, test for existence safely, and break complex questions into named steps with CTEs and recursive CTEs. SQL & Databases roadmap · Advanced SQL · 18 min read · https://levelcode.org/roadmaps/sql/subqueries-and-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: ```sql SELECT title, price FROM books WHERE price > (SELECT avg(price) FROM books) ORDER BY price DESC; ``` ```text 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: ```sql SELECT title FROM books WHERE price = (SELECT price FROM books WHERE genre = 'fantasy'); ``` ```text 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: ```sql 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; ``` ```text 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`): ```sql 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; ``` ```text 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`: ```sql 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; ``` ```text 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 `NULL`s 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: ```sql SELECT name FROM customers WHERE customer_id NOT IN (SELECT referred_by FROM customers); ``` ```text 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: ```sql 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; ``` ```text name -------------- Chloe Martin Elena Rossi Farid Haddad (3 rows) ``` > [!WARNING] > Never use `NOT IN (subquery)` unless the subquery's column is declared `NOT NULL`. Use `NOT EXISTS` — it is `NULL`-safe, and PostgreSQL plans it as an efficient anti-join. ## 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: ```sql 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; ``` ```text 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: ```sql 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; ``` ```text 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: ```sql 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; ``` ```text 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. > [!TIP] > Reach for a CTE as soon as a query has more than one logical step. Name each CTE after what one of its rows represents (`order_totals`, `valid_orders`), and the main query almost documents itself. ## 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? ```sql 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; ``` ```text 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. > [!IMPORTANT] > If the data contains a cycle (A referred B, B referred A), a recursive CTE loops forever. Guard against it with a depth limit in the recursive step (`WHERE rt.depth < 20`) or PostgreSQL's `CYCLE customer_id SET is_cycle USING visited` clause, placed after the CTE's closing parenthesis. ## 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. **Solution** ```sql 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; ``` ```text 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) ``` The inner query needs its own alias (`b2`) so that `b.genre` unambiguously means the outer row. ```sql 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; ``` ```text name -------------- Ben Okafor Chloe Martin Elena Rossi Farid Haddad (4 rows) ``` ```sql 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; ``` ```text 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) ``` The average of the nine valid order totals is about 28.85. The CTE is referenced twice — once in `FROM` and once in the scalar subquery — which a plain derived table could not do without repeating itself. ## 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. ## Quiz 1. customers.referred_by contains some NULLs. What does SELECT name FROM customers WHERE customer_id NOT IN (SELECT referred_by FROM customers); return? A. Customers who have not referred anyone B. Customers who have referred someone C. No rows at all D. An error because of the NULLs **Answer: C.** For every customer the condition includes customer_id <> NULL, which is unknown, so NOT IN can never be true and no rows qualify. NOT EXISTS gives the intended answer. 2. A subquery used as a single value in WHERE price = (SELECT ...) returns three rows. What happens? A. The query fails with 'more than one row returned by a subquery used as an expression' B. The first row is used C. It behaves like IN and matches any of them D. The comparison is NULL and no rows match **Answer: A.** A scalar subquery must return at most one row; PostgreSQL raises an error at run time otherwise. Use IN (or = ANY) if you mean 'matches any of these values'. 3. What are the two parts of a recursive CTE, and how are they combined? A. A SELECT and a WHERE, combined with AND B. Two CTEs joined with JOIN LATERAL C. A base table and a view, combined with INTERSECT D. An anchor query and a recursive query that references the CTE, combined with UNION ALL **Answer: D.** The anchor produces the starting rows; the recursive term joins the CTE to the table to produce each next level, repeating until it returns no rows. UNION (without ALL) is also allowed and removes duplicates. 4. In PostgreSQL 17, how is a non-recursive, side-effect-free CTE that is referenced exactly once planned by default? A. It is always computed first and stored in a temporary table B. It is inlined into the main query like a subquery C. It is cached across queries in the same session D. It is executed once per row of the main query **Answer: B.** Since PostgreSQL 12, such CTEs are inlined so the planner can optimise across them, for example pushing filters down into the CTE. Add MATERIALIZED to force the old compute-once behaviour.