Session 7 — Subqueries: scalar, IN, EXISTS, correlated

A subquery is a SELECT nested inside another query — how a single query answers questions like "pricier than the average?" or "never sold anything?". You will master four shapes: scalar subqueries (one value, usable anywhere a value fits), IN subqueries (one column, many rows), EXISTS tests (yes/no per row), and correlated subqueries (the inner query re-reads the outer row, so it runs once per outer row). You will also learn the NOT IN + NULL trap and when a JOIN beats a subquery.

Plan: Core ideas → worked nesting → Quiz.

Time: about 70–80 minutes if you compute every inner result before reading the outer filter.

Session 7 — Subqueries: scalar, IN, EXISTS, correlated

The one-paragraph story

A subquery is a SELECT nested inside another query, and it answers the questions a single query cannot: "which books are more expensive than the average?", "which authors never sold anything?", "which books cost more than every book by Priya Anand?". There are four shapes to master — scalar subqueries (return one value), IN subqueries (one column, many rows), EXISTS subqueries (a yes/no test per row), and correlated subqueries (the inner query re-reads the outer row). Each shape plugs into a different slot of the outer query, and each has a distinct mental model.

---

Scalar subqueries: one value

A scalar subquery returns exactly one row and one column — a single value. It can be used anywhere a value is allowed, e.g. in a comparison:

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

The inner query evaluates first, once: AVG(price) for the six books is 23.915. The outer query then keeps books with price > 23.915: Salt Roads (31.00) and North of Noon (27.50). Read the parentheses as "compute this value first".

A scalar subquery can also sit in the SELECT list, adding a computed column that is the same for every row:

SELECT title,
       price - (SELECT AVG(price) FROM books) AS above_avg
FROM books;

Warning: if a scalar subquery returns more than one row, the engine raises an error — a scalar must produce exactly one value.

---

IN subqueries: one column, many rows

IN tests whether a value is present among the rows a subquery returns. The inner query returns one column (possibly many rows):

SELECT title
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'Germany');

Inner query first: German authors are {1, 4}. Outer query then keeps books whose author_id is in that set — The Glass Harbour, Static Echoes (author 1) and Prairie Circuit (author 4).

NOT IN is the mirror, but with a sharp edge: if the subquery returns any NULL, NOT IN matches nothing (the "unknown" logic from Session 5 again). Prefer NOT EXISTS when NULLs are possible.

---

EXISTS: a per-row yes/no test

EXISTS asks "does anything match?" and returns true/false — it does not carry values back at all:

SELECT a.name
FROM authors a
WHERE EXISTS (
    SELECT 1
    FROM books b
    WHERE b.author_id = a.author_id
);

The inner query references the outer table's row (a.author_id) — it is correlated. For each author row, the engine asks: does a book with this author_id exist? If yes, the author is kept. Convention writes SELECT 1 because the value is irrelevant — only existence matters.

---

Correlated subqueries: the inner query sees the outer row

A correlated subquery is re-evaluated for each outer row, because it references outer columns. The classic "above their own average" or "more expensive than everything in their category":

SELECT b.title, b.price
FROM books b
WHERE b.price > (
    SELECT AVG(price)
    FROM books
    WHERE genre = b.genre
);

For each book, compute the average price of that book's genre and keep the book if it exceeds it. The inner query is generic until the engine plugs in the current outer book's genre — that is the correlation. Uncorrelated subqueries compute once; correlated ones compute per outer row. (Note: in this schema a genre has only two books, so the comparison reads "is this book the pricier of its pair" — fiction's avg is 22.245, history 29.25, mystery 20.00, so the kept books are The Glass Harbour, Salt Roads, and River Clock.)

---

WHERE subqueries vs JOINs

Many "use subquery" questions can also be expressed as joins:

-- German authors' books, subquery style
SELECT title FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'Germany');

-- Same answer, join style
SELECT b.title
FROM books b
JOIN authors a ON b.author_id = a.author_id
WHERE a.country = 'Germany';

Both return The Glass Harbour, Static Echoes, Prairie Circuit. Use whichever reads more clearly; the engine often executes them identically. The tie-breaker: EXISTS/IN shine when the inner side is about existence, joins shine when you actually need the right table's columns.

---

Pitfalls to watch for

• A scalar subquery must return exactly one value — multiple rows is an error.
• IN needs one column — two columns in the inner SELECT breaks the comparison.
• NOT IN with NULLs returns nothing — switch to NOT EXISTS when the inner set can contain NULL.
• Correlated subqueries run per outer row — a habit worth forming: if the inner query references the outer alias, it is correlated, and you should say so when you trace it.

Session 7 — Worked examples

The bookshop data:

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

sales: (100, 10, 2), (101, 12, 1), (102, 10, 3), (103, 14, 1), (104, 13, 2) — (sale_id, book_id, qty)

Example A — Scalar subquery: above average

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

Inner first: AVG(price) = 23.915 (143.49 / 6). Outer filter keeps prices above 23.915:

| title | price | |-------|-------| | Salt Roads | 31.00 | | North of Noon | 27.50 |

Trace discipline: compute the inner value once, write it at the top of your page, then apply it to every row.

---

Example B — Scalar subquery in the SELECT list

SELECT title,
       price - (SELECT AVG(price) FROM books) AS above_avg
FROM books
ORDER BY above_avg DESC;

Each row's above_avg = price − 23.915:

| title | above_avg | |-------|-----------| | Salt Roads | 7.085 | | North of Noon | 3.585 | | The Glass Harbour | 1.075 | | River Clock | −1.915 | | Static Echoes | −4.415 | | Prairie Circuit | −5.915 |

Same computed value subtracted from every row — the scalar ran once.

---

Example C — IN subquery

SELECT title
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'Germany');

Inner: {1, 4}. Outer keeps books 10, 11 (author 1) and 15 (author 4):

| title | |-------| | The Glass Harbour | | Static Echoes | | Prairie Circuit |

---

Example D — NOT IN and its NULL trap

SELECT title
FROM books
WHERE author_id NOT IN (SELECT author_id FROM books WHERE genre = 'history');

Inner set is {3} (authors of history books). NOT IN keeps books whose author_id is not 3 → The Glass Harbour, Static Echoes (1), River Clock (2), Prairie Circuit (4). Works.

Now imagine the inner query were SELECT author_id FROM authors WHERE country = 'Germany' — no NULLs — still fine. But if the inner set can contain NULL, e.g. SELECT mentor_id FROM authors with author 1 having mentor_id = NULL, then NOT IN (1, 4, NULL) returns nothing: for any book, author_id ≠ NULL is unknown, so no row passes. The safe rewrite:

SELECT title
FROM books b
WHERE NOT EXISTS (
    SELECT 1 FROM authors a
    WHERE a.author_id = b.author_id
      AND a.country = 'Germany'
);

---

Example E — EXISTS: authors who have books

SELECT a.name
FROM authors a
WHERE EXISTS (
    SELECT 1 FROM books b WHERE b.author_id = a.author_id
);

Per author: does any book carry this author_id? All four authors do (each wrote at least one book), so all four names return. If we added a book-less author, EXISTS would skip her — while NOT EXISTS would be the way to find her.

---

Example F — Correlated: pricier than the genre average

SELECT b.title, b.genre, b.price
FROM books b
WHERE b.price > (
    SELECT AVG(price)
    FROM books
    WHERE genre = b.genre
);

Per genre average: fiction 22.245, mystery 20.00, history 29.25. Books above their genre average:

| title | genre | price | |-------|-------|-------| | The Glass Harbour | fiction | 24.99 | | River Clock | mystery | 22.00 | | Salt Roads | history | 31.00 |

Verify River Clock: mystery avg 20.00, 22.00 > 20.00 ✓. The correlation (genre = b.genre) is what recomputes the average for each book.