SQL Subqueries and CTEs (WITH)

Some questions cannot be answered in one pass. "Which tools cost more than average?" needs the average before it can compare anything to it. "Which members have never borrowed a power tool?" needs the list of power-tool borrowers before it can exclude them. Both are one question built on top of another, and SQL lets you write them that way: a query can contain another query.

There are two spellings for the same idea. A subquery sits inline, inside the clause that needs it. A common table expression — a CTE, written with WITH — is given a name at the top of the statement and used by name below. The second is usually easier to read, and the rest of this lesson builds up to it.

Every example creates its own tables, because each run in this playground starts from an empty database.

A subquery that returns one value

The simplest subquery returns exactly one row and one column, so it can be used anywhere a single value could go.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT name, deposit
FROM tools
WHERE deposit > (SELECT avg(deposit) FROM tools)
ORDER BY deposit DESC;

name           | deposit
---------------+--------
tile cutter    |    1500
cordless drill |    1200
hedge trimmer  |     950
(3 rows)

The inner query works out 900, and the outer query compares every row against it. Writing WHERE deposit > avg(deposit) instead would be an error, because an aggregate cannot be tested in WHERE — the subquery is what lets the average be computed separately and then used as a plain number.

A scalar subquery can also sit in the SELECT list, which is handy for showing a row alongside a total it belongs to:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500);

SELECT
  name,
  deposit,
  (SELECT sum(deposit) FROM tools) AS all_deposits,
  round(deposit * 100.0 / (SELECT sum(deposit) FROM tools), 1) AS percent_of_total
FROM tools
ORDER BY deposit DESC;

name           | deposit | all_deposits | percent_of_total
---------------+---------+--------------+-----------------
cordless drill |    1200 |         2650 |             45.3
hedge trimmer  |     950 |         2650 |             35.8
wheelbarrow    |     500 |         2650 |             18.9
(3 rows)

Both subqueries here are independent of the row being processed, so the database works them out once. A subquery that does refer to the outer row is called correlated, and it runs conceptually once per row — which matters for speed on a big table, and not at all on five rows.

IN, NOT IN and EXISTS

A subquery that returns a column of values can feed IN, which is where the version of IN from lesson 2 becomes properly useful.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12);

SELECT name
FROM members
WHERE id IN (
  SELECT l.member_id
  FROM loans AS l
  JOIN tools AS t ON t.id = l.tool_id
  WHERE t.shelf = 'power'
)
ORDER BY name;

name
----
Asha
Cleo
(2 rows)

The inner query lists the member ids that appear on a power-tool loan; the outer one keeps the members whose id is in that list. NOT IN inverts it, and EXISTS is the third way to ask a related question:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power');
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12);

SELECT name
FROM members AS m
WHERE NOT EXISTS (
  SELECT 1
  FROM loans AS l
  JOIN tools AS t ON t.id = l.tool_id
  WHERE l.member_id = m.id AND t.shelf = 'power'
)
ORDER BY name;

name
-----
Bruno
Devi
(2 rows)

EXISTS takes a subquery and asks only whether it produced any row at all — which is why the inner SELECT 1 is conventional: the value is never looked at. WHERE l.member_id = m.id is the correlation, and it is what makes the inner query a question about this member.

Prefer NOT EXISTS to NOT IN when the inner column might contain NULL. NOT IN against a list holding a single NULL returns no rows at all, for the reason lesson 2 showed, and it does so silently. NOT EXISTS is not affected:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo');
-- One loan was recorded without a member: a genuine data-entry gap.
INSERT INTO loans (member_id) VALUES (1), (2), (NULL);

SELECT name AS never_borrowed_not_in
FROM members
WHERE id NOT IN (SELECT member_id FROM loans);

SELECT name AS never_borrowed_not_exists
FROM members AS m
WHERE NOT EXISTS (SELECT 1 FROM loans AS l WHERE l.member_id = m.id);

never_borrowed_not_in
---------------------
(0 rows)

never_borrowed_not_exists
-------------------------
Cleo
(1 row)

Same intent, two different answers, and the first one is wrong. Cleo has never borrowed anything, and the NOT IN version lost her because of one NULL in a table she has nothing to do with.

A subquery in FROM: the derived table

A subquery can also stand in for a table. It needs a name, and then it behaves exactly like one.

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12), (2, 10);

SELECT avg(loan_count) AS mean_loans_per_member, max(loan_count) AS busiest
FROM (
  SELECT member_id, count(*) AS loan_count
  FROM loans
  GROUP BY member_id
) AS per_member;

mean_loans_per_member | busiest
----------------------+--------
                    2 |       3
(1 row)

That is an aggregate of an aggregate, which cannot be written in one pass: avg(count(*)) is not legal SQL. The inner query produces one row per member; the outer query then averages those rows.

WITH: the same thing, readable

Nesting works, and stops working the moment there are three levels of it. WITH lifts each step out, names it, and lets you read the query top to bottom.

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12), (2, 10);

WITH per_member AS (
  SELECT member_id, count(*) AS loan_count
  FROM loans
  GROUP BY member_id
)
SELECT avg(loan_count) AS mean_loans_per_member, max(loan_count) AS busiest
FROM per_member;

mean_loans_per_member | busiest
----------------------+--------
                    2 |       3
(1 row)

Identical result, and now the steps have names. A CTE can also be referred to more than once in the same statement, which a derived table cannot, and several can be defined at once by separating them with commas. Each one can use the ones above it, so writing them in the order they are used keeps the query readable:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO tools (id, name, shelf, deposit) VALUES
  (10, 'hedge trimmer', 'garden', 950), (11, 'cordless drill', 'power', 1200),
  (12, 'wheelbarrow', 'garden', 500), (13, 'tile cutter', 'power', 1500);
INSERT INTO loans (member_id, tool_id) VALUES (1, 10), (1, 11), (2, 12), (3, 11), (1, 12);

WITH loan_value AS (
  SELECT l.member_id, t.deposit
  FROM loans AS l
  JOIN tools AS t ON t.id = l.tool_id
),
member_totals AS (
  SELECT member_id, count(*) AS loans, sum(deposit) AS deposit_value
  FROM loan_value
  GROUP BY member_id
)
SELECT m.name, COALESCE(mt.loans, 0) AS loans, COALESCE(mt.deposit_value, 0) AS deposit_value
FROM members AS m
LEFT JOIN member_totals AS mt ON mt.member_id = m.id
ORDER BY deposit_value DESC, m.name;

name  | loans | deposit_value
------+-------+--------------
Asha  |     3 |          2650
Cleo  |     1 |          1200
Bruno |     1 |           500
Devi  |     0 |             0
(4 rows)

Three steps, each readable on its own: pair every loan with its tool's deposit, total those per member, then list every member against their total. Devi appears with two zeroes because of the LEFT JOIN and the COALESCE, exactly as in lesson 5.

A note on portability: PostgreSQL has had WITH for years, MySQL only since version 8.0, and SQLite since 3.8.3 — so a CTE is safe almost everywhere today, but not in a very old MySQL.

Aggregate first, then join

This is the pattern that fixes the fan-out problem from the joins lesson, and it is worth learning as a shape rather than as a trick. Summarise the many-side in a CTE so it has one row per key, then join it.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, deposit_held INTEGER);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO members (id, name, deposit_held) VALUES (1, 'Asha', 2000), (2, 'Bruno', 500);
INSERT INTO loans (member_id) VALUES (1), (1), (1), (2);

SELECT sum(m.deposit_held) AS inflated_total
FROM members AS m
JOIN loans AS l ON l.member_id = m.id;

WITH loan_counts AS (
  SELECT member_id, count(*) AS loans
  FROM loans
  GROUP BY member_id
)
SELECT sum(m.deposit_held) AS honest_total, sum(lc.loans) AS loans
FROM members AS m
JOIN loan_counts AS lc ON lc.member_id = m.id;

inflated_total
--------------
          6500
(1 row)

honest_total | loans
-------------+------
        2500 |     4
(1 row)

The first total counts Asha's deposit three times because she has three loans. The second joins against a table that has exactly one row per member, so nothing multiplies — and the loan count is still right, because it was worked out before the join rather than after it. Whenever you need a total from one table and a count from another, this shape is the answer.

Recursive CTEs, briefly

A CTE may refer to itself, which is how SQL walks a hierarchy or generates a series. The shape is always the same: a starting row, UNION ALL, and a step that builds on what is already there.

WITH RECURSIVE weeks(week_start) AS (
  SELECT '2026-03-02'
  UNION ALL
  SELECT date(week_start, '+7 days') FROM weeks WHERE week_start < '2026-03-30'
)
SELECT week_start FROM weeks;

week_start
----------
2026-03-02
2026-03-09
2026-03-16
2026-03-23
2026-03-30
(5 rows)

date(week_start, '+7 days') is SQLite's date arithmetic on a YYYY-MM-DD string; PostgreSQL would write week_start + interval '7 days'. The WHERE inside the recursive half is the stopping condition, and it is not optional: without it the CTE never finishes. This playground shows at most 1,000 rows per query and stops reading there, so a runaway recursive CTE gets cut off rather than filling memory — but it will still hit the run timeout if the rows are expensive, so write the stopping condition first and check it.

Two portability notes: PostgreSQL requires the word RECURSIVE, while SQLite accepts a self-referencing CTE with or without it, so include it and the query reads the same everywhere. And MySQL before 8.0 has no recursive CTEs at all.

A worked example

The busiest shelf per member, using a CTE for each step:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, tool_id INTEGER, taken_on TEXT);

INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno'), (3, 'Cleo'), (4, 'Devi');
INSERT INTO tools (id, name, shelf) VALUES
  (10, 'hedge trimmer', 'garden'), (11, 'cordless drill', 'power'),
  (12, 'wheelbarrow', 'garden'), (13, 'tile cutter', 'power'), (14, 'hand saw', 'hand');
INSERT INTO loans (member_id, tool_id, taken_on) VALUES
  (1, 10, '2026-03-04'), (1, 12, '2026-03-21'), (1, 11, '2026-04-08'),
  (2, 12, '2026-03-19'), (2, 14, '2026-04-11'),
  (3, 11, '2026-04-02');

WITH shelf_counts AS (
  SELECT l.member_id, t.shelf, count(*) AS loans
  FROM loans AS l
  JOIN tools AS t ON t.id = l.tool_id
  GROUP BY l.member_id, t.shelf
),
favourite AS (
  SELECT member_id, shelf, loans
  FROM shelf_counts AS sc
  WHERE loans = (
    SELECT max(loans) FROM shelf_counts AS inner_sc WHERE inner_sc.member_id = sc.member_id
  )
)
SELECT
  m.name,
  COALESCE(f.shelf, '-')  AS favourite_shelf,
  COALESCE(f.loans, 0)    AS loans_from_it
FROM members AS m
LEFT JOIN favourite AS f ON f.member_id = m.id
ORDER BY m.name, f.shelf;

name  | favourite_shelf | loans_from_it
------+-----------------+--------------
Asha  | garden          |             2
Bruno | garden          |             1
Bruno | hand            |             1
Cleo  | power           |             1
Devi  | -               |             0
(5 rows)

Bruno appears twice, and that is the correct answer rather than a bug: he has one garden loan and one hand loan, so he has two equally favourite shelves and the WHERE loans = (SELECT max(loans) ...) keeps both. If you want exactly one row per member you have to decide how to break the tie, which is what window functions are for. Note also that shelf_counts is used twice — once in favourite's FROM and once inside its correlated subquery — which is the thing a derived table cannot do.

Common mistakes

A subquery that returns more than one value where one was expected

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500);

SELECT name FROM tools WHERE deposit = (SELECT deposit FROM tools WHERE shelf = 'garden');

name
-------------
hedge trimmer
(1 row)

Two garden tools, so the subquery returns two rows, and = has no idea what to do with that. SQLite quietly uses the first row it happens to get and carries on — so this returns an answer that is right by accident and will change when the data does. PostgreSQL raises an error instead. Use IN when the subquery can return several rows, or an aggregate such as min() when you genuinely want one value.

Expecting a CTE to survive into the next statement

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO loans (member_id) VALUES (1), (1), (2);

WITH per_member AS (
  SELECT member_id, count(*) AS loan_count FROM loans GROUP BY member_id
)
SELECT member_id, loan_count FROM per_member ORDER BY member_id;

SELECT max(loan_count) AS busiest FROM per_member;

member_id | loan_count
----------+-----------
        1 |          2
        2 |          1
(2 rows)

Error in the statement on line 9: no such table: per_member

The first query worked and printed its table. The second failed, because a CTE belongs to the one statement that defines it — the semicolon ends its life. It is not a temporary table and nothing was stored. If two statements need the same intermediate result, either repeat the WITH clause in both or create a real table for it, which the next lesson covers.

This example is also the first here where an error follows a successful table. The playground prints everything the statements produced up to the failure, then a blank line, then the error — so a partial result is a clue about how far the script got.

Shadowing a table name you still need

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO members (id, name) VALUES (1, 'Asha'), (2, 'Bruno');
INSERT INTO loans (member_id) VALUES (1);

SELECT m.name
FROM members AS m
WHERE EXISTS (SELECT 1 FROM loans AS l WHERE l.member_id = members.id);

Error in the statement on line 6: no such column: members.id

Once a table is given an alias, the alias is the only name it answers to: members no longer exists inside the statement, so the correlated condition has nothing to point at. Write m.id. The habit that avoids this entirely is to alias every table in a query, or none of them, rather than half.

Putting a CTE after the SELECT

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER);
INSERT INTO loans (member_id) VALUES (1), (1), (2);

SELECT * FROM per_member
WITH per_member AS (SELECT member_id, count(*) AS n FROM loans GROUP BY member_id);

Error in the statement on line 4: near "per_member": syntax error

WITH comes first, before the SELECT that uses it — the definition is at the top of the statement even though it reads like a footnote. A single statement can only have one WITH clause, and everything it defines goes in there separated by commas.

Next steps

You can now build a query out of named steps, which is the difference between SQL you can come back to in six months and SQL you rewrite. Two things are still awkward: ranking rows within a group, and running totals. Both need a function that can see the other rows in a group without collapsing them, which is exactly what window functions do. Before that, changing data safely covers writing to a table rather than reading from one. Take the worked example into the SQL playground and give Bruno a second garden loan to see his tie resolve.