Subqueries and self joins

A subquery is a SELECT inside another statement. There are four places one can go, and knowing which to reach for is most of the skill — the same question frequently has a subquery answer and a join answer, and they are not always equally good.

Four places

-- 1. in WHERE, as a list
WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL)

-- 2. in WHERE, as a yes/no test
WHERE EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.id)

-- 3. in SELECT, producing one value per row
SELECT b.title, (SELECT count(*) FROM loans l WHERE l.book_id = b.id) AS times

-- 4. in FROM, as a table
FROM (SELECT book_id, count(*) AS n FROM loans GROUP BY book_id) t

Correlated or not

The distinction that decides performance.

Uncorrelated — it does not mention the outer query, so it runs once:

SELECT title FROM books
WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL);

Correlated — it refers to the outer row, so conceptually it runs once per outer row:

SELECT b.title,
       (SELECT count(*) FROM loans l WHERE l.book_id = b.id) AS times_borrowed
FROM books b
ORDER BY times_borrowed DESC
LIMIT 3;

          title          | times
-------------------------+-------
 An Equal Music          |    11
 Interpreter of Maladies |     9
 Unaccustomed Earth      |     9
(3 rows)

Forty books, so forty counts. PostgreSQL's planner frequently rewrites a correlated subquery into a join and does it once — but not always, and "conceptually once per row" is the right mental model for deciding whether to worry. Module 7 shows how to check which happened.

EXISTS, and why it is usually the right one

SELECT count(*) AS books_out_now FROM books b
WHERE EXISTS (
  SELECT 1 FROM loans l WHERE l.book_id = b.id AND l.returned_on IS NULL
);

 books_out_now
---------------
            26

SELECT 1 because nothing looks at the value — EXISTS only asks whether a row came back. SELECT * would work identically; 1 says "I do not care what" and is the convention.

Three reasons EXISTS beats the alternatives:

It cannot fan out. A join to loans would return a book once per outstanding loan and you would need DISTINCT.

It short-circuits. The database stops at the first matching row rather than counting them all.

NOT EXISTS is safe with NULL, where NOT IN is not — from module 2, where 14 became 0 because of one NULL.

Rule: for "is there any", use EXISTS. For "is there none", use NOT EXISTS. Always.

When a join is better than a subquery

When you need columns from the other table:

-- awkward: one subquery per column
SELECT b.title,
       (SELECT a.name FROM authors a WHERE a.id = b.author_id) AS author,
       (SELECT a.language FROM authors a WHERE a.id = b.author_id) AS language
FROM books b;

-- better
SELECT b.title, a.name AS author, a.language
FROM books b JOIN authors a ON a.id = b.author_id;

Subqueries answer questions. Joins bring columns. If you are writing two correlated subqueries against the same table, you want a join.

Subqueries in FROM, which are underrated

A subquery in FROM is a table. This is the clean answer to the fan-out problem:

SELECT b.title, b.copies, COALESCE(t.n, 0) AS times_borrowed
FROM books b
LEFT JOIN (
  SELECT book_id, count(*) AS n FROM loans GROUP BY book_id
) t ON t.book_id = b.id
ORDER BY times_borrowed DESC;

The aggregation happens before the join, so b.copies is not multiplied and can be summed safely. This is the "aggregate before you join" habit, written out.

A subquery in FROM must have an alias — t here — or PostgreSQL rejects it.

WITH, which is the same thing but readable

WITH borrow_counts AS (
  SELECT book_id, count(*) AS n FROM loans GROUP BY book_id
),
popular AS (
  SELECT book_id FROM borrow_counts WHERE n > 5
)
SELECT b.title, bc.n
FROM books b
JOIN borrow_counts bc ON bc.book_id = b.id
JOIN popular p        ON p.book_id = b.id
ORDER BY bc.n DESC;

A common table expression. Same result as nesting, and you can read it top to bottom instead of inside out. Each CTE can refer to earlier ones.

Use WITH whenever a query needs more than one subquery or is longer than about fifteen lines. The readability gain is large and free.

One historical note: before PostgreSQL 12, a CTE was always materialised — computed fully and stored — which acted as an optimisation fence and could be much slower. From 12 onwards it is inlined when that is better, and you can force either with AS MATERIALIZED or AS NOT MATERIALIZED. Advice written before 2019 saying "avoid CTEs for performance" is out of date.

Self joins

A table joined to itself, which needs aliases to distinguish the two copies:

SELECT a1.name, a2.name, a1.language
FROM authors a1
JOIN authors a2 ON a2.language = a1.language AND a2.id > a1.id
ORDER BY a1.language, a1.name
LIMIT 5;

        name         |      name      | language
---------------------+----------------+----------
 Rabindranath Tagore | Mahasweta Devi | Bengali
 Amitav Ghosh        | Sudha Murty    | English
 Anita Desai         | Amitav Ghosh   | English
 Anita Desai         | Sudha Murty    | English
 Anita Desai         | Kiran Desai    | English
(5 rows)

a2.id > a1.id is doing two jobs: it stops each author pairing with themselves, and it returns each pair once rather than twice in both orders. Without it you would get Tagore–Devi and Devi–Tagore, plus Tagore–Tagore.

The other common self join is a hierarchy:

SELECT c.name AS category, p.name AS parent
FROM categories c LEFT JOIN categories p ON p.id = c.parent_id;

LEFT JOIN because a top-level category has no parent.

Recursive CTEs, for a tree of unknown depth

A self join goes one level. For "all descendants, however deep":

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL        -- the anchor
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON t.id = c.parent_id   -- the recursion
)
SELECT repeat('  ', depth - 1) || name AS tree FROM tree ORDER BY name;

The anchor selects the starting rows; the recursive part joins back to the results so far; UNION ALL combines them until nothing new is produced.

This is the thing the families lesson said to try before reaching for a graph database. It handles organisation charts, category trees, threaded comments and bills of materials — the large majority of what people want a graph database for.

Guard against infinite recursion: if the data can contain a cycle, carry a path array and exclude anything already visited.

Lateral joins, briefly

SELECT m.name, l.title, l.borrowed_on
FROM members m
CROSS JOIN LATERAL (
  SELECT b.title, l.borrowed_on
  FROM loans l JOIN books b ON b.id = l.book_id
  WHERE l.member_id = m.id
  ORDER BY l.borrowed_on DESC
  LIMIT 3
) l;

LATERAL lets a subquery in FROM refer to columns from earlier in the FROM. It is the clean way to express "the top N per group" — the three most recent loans for each member — which is otherwise awkward.

Worth knowing the word. You will want it within a month of needing "latest per group".

Choosing

Is there any / is there none?          EXISTS / NOT EXISTS
I need columns from the other table    JOIN
One computed value per row             a correlated subquery in SELECT — or a join
                                       to a pre-aggregated subquery, if it is slow
Aggregate then join                    a subquery in FROM, or a CTE
More than one step, or long            WITH
A tree of unknown depth                WITH RECURSIVE
Top N per group                        LATERAL

Check your work

The four places a subquery can go. WHERE, SELECT, FROM, and as a yes/no test.

Correlated versus uncorrelated. Mentions the outer row and conceptually runs per row, versus runs once.

Why SELECT 1 in an EXISTS. Nothing looks at the value.

Three reasons EXISTS wins. No fan-out, it short-circuits, and NOT EXISTS is NULL-safe.

When a join beats a subquery. When you need columns, not an answer.

What a subquery in FROM fixes. Fan-out — the aggregation happens before the join.

What it must have. An alias.

When to use WITH. More than one subquery, or more than about fifteen lines.

What changed in PostgreSQL 12. CTEs are inlined when better, so old "avoid CTEs" advice is stale.

What a2.id > a1.id does in a self join. Stops self-pairing and returns each pair once.

What a recursive CTE is for. A tree of unknown depth — and it is what to try before a graph database.

What LATERAL is for. Top N per group.

Practice

1. Find books currently out using IN with a subquery, then using EXISTS. Compare.
2. Find books never borrowed using NOT EXISTS. Then try NOT IN and add a NULL to the subquery to break it.
3. Write a correlated subquery giving each book's borrow count. Then write it as a join to a pre-aggregated subquery.
4. Write two correlated subqueries against authors for two columns, then rewrite as a join.
5. Compute sum(copies) joined to a pre-aggregated loan count and confirm it is 95.
6. Rewrite a two-subquery query using WITH. Read both aloud.
7. Look up AS MATERIALIZED and force a CTE both ways.
8. Write the self join for authors sharing a language. Remove a2.id > a1.id and count the extra rows.
9. Add a parent_id to categories, build a two-level tree, and query it with a self join.
10. Then query it with a recursive CTE to any depth.
11. Add a cycle to that data and watch the recursion run away. Add a path guard.
12. Use LATERAL to get each member's three most recent loans.
13. Take the choosing table and write one query for each row against this database.

Official documentation

• PostgreSQL — Subquery expressions — EXISTS, IN, ANY, ALL and their NULL behaviour.
• PostgreSQL — WITH queries — CTEs, RECURSIVE, and the materialisation rules changed in 12.
• PostgreSQL — LATERAL subqueries — Including the top-N-per-group pattern.
• PostgreSQL — Table aliases — Why a self join needs them.

---

You can now connect tables: foreign keys that guarantee the connection, every kind of join, many-to-many through a join table, and subqueries for the questions a join answers badly.

Next module: changing data, and what happens when two people change it at once.