---
title: Subqueries
source: 'https://academia.sh/en/courses/advanced-sql/subqueries'
course: 'Advanced SQL'
language: en
updated: '2026-08-23T07:00:37+00:00'
license: 'CC BY-SA 4.0'
---

# Subqueries

Where scalar, row, and table subqueries are written, derived tables, the difference between IN and EXISTS, and a null value silently emptying a subquery's result.

The SQL Fundamentals course built statements that produce a single result set: selecting
rows, joining tables, grouping and summarizing. What these statements share is that the
computation finishes in **one step**. Real questions, though, are often two steps: the
question "members who borrowed more than the average" first needs the average, then
needs whichever members exceed it.

Questions like this can be answered with two separate queries whose results are combined
by hand, but the data can change between queries and carrying an intermediate result over
is wasted work. SQL's answer is to place one query inside another. The previous course
used this placement only to write a condition; here it is treated as a construct in its
own right. This lesson builds the forms that placement takes and where each one can be
written.

## Subqueries and the Shape They Return

A **subquery** is a `SELECT` statement embedded in parentheses inside another statement.
The one thing that determines where it can be written is the **shape it returns**:

| Shape | What it returns | Where it is used |
|---|---|---|
| Scalar subquery | One row, one column | Anywhere a single value can appear |
| Row subquery | One row, many columns | In row comparisons |
| Table subquery | Many rows, many columns | `FROM`, `IN`, `EXISTS` |

This triple is the skeleton of the lesson. When the shape does not match, the engine
raises an error; when the shape matches but the result is unexpected, a null value is
almost always the cause — the last section of the lesson takes that up.

The same example runs through the whole course: a library's loan records. There are four
tables — `branch`, `member`, `book`, and `loan`. Each code block sets up the tables it
needs on its own; copied and run as written, a block produces the output shown.

## Scalar Subquery

A scalar subquery produces exactly one value, so it can be written anywhere a constant or
a column name would be written. Its most common place is the right side of a comparison.

The question "members who borrowed more books than the average member" is expressed with
a scalar subquery inside `HAVING`:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch_id INT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice',4),(2,'Ben',4),(3,'Clara',5),(4,'Derek',5),
                       (5,'Evan',6),(6,'Fiona',6),(7,'Grace',4),(8,'Hannah',5);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT m.name, COUNT(*) AS count
FROM loan l JOIN member m ON m.id = l.member_id
GROUP BY m.id, m.name
HAVING COUNT(*) > (SELECT COUNT(*) * 1.0 / COUNT(DISTINCT member_id) FROM loan)
ORDER BY count DESC, m.name;
SQL
```

```text
┌───────┬───────┐
│ name  │ count │
├───────┼───────┤
│ Alice │ 5     │
│ Ben   │ 4     │
│ Clara │ 4     │
└───────┴───────┘
```

The subquery does not reference any column of the outer query. This independence has two
consequences: the engine evaluates it **once** and treats the result as a constant, and
the subquery can be run by itself and verified in isolation. Once a link is drawn between
the subquery and the outer query the picture changes — that is the subject of the next
lesson.

The scalar subquery's contract is strict: it must return **at most one row**. If it
returns zero rows the result is a null value; if it returns more than one row, the
standard requires an error. For this reason scalar subqueries either contain an
aggregate function or are filtered down to a key.

## Row Subquery

A comparison does not have to be limited to single values. Standard SQL also compares row
values: the parenthesized column list on the left is matched, column by column, against
the row a subquery on the right returns.

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (2,3,1,'2024-03-01','2024-03-20'),(11,3,3,'2024-03-06','2024-03-27'),
  (22,3,6,'2024-03-20',NULL),(3,5,1,'2024-03-04','2024-03-18');
SELECT id, book_id, member_id, pickup
FROM loan
WHERE (book_id, pickup) = (SELECT book_id, MIN(pickup) FROM loan WHERE book_id = 3);
SQL
```

```text
┌────┬─────────┬───────────┬────────────┐
│ id │ book_id │ member_id │   pickup   │
├────┼─────────┼───────────┼────────────┤
│ 2  │ 3       │ 1         │ 2024-03-01 │
└────┴─────────┴───────────┴────────────┘
```

Row comparison writes a question that holds two conditions together, such as "the
**first** loan of this book," as a single expression. The same result can be reached with
two separate scalar subqueries, but that phrasing visually loses the fact that both
subqueries come from the same row.

Row value comparison is standard, but the degree of support varies by engine, and it
varies further when combined with ordering operators (`<`, `>`). If portable code is
being written, the target engine's support has to be confirmed.

## Table Subquery and Derived Tables

A subquery that returns many rows behaves like a table. It has two uses.

The first is appearing inside `FROM` as a **derived table**. This is the standard way to
write two-layer computations, such as regrouping the result of a grouping:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT MIN(count) AS minimum, MAX(count) AS maximum, ROUND(AVG(count), 2) AS average
FROM (SELECT member_id, COUNT(*) AS count FROM loan GROUP BY member_id) AS tally;
SQL
```

```text
┌─────────┬─────────┬─────────┐
│ minimum │ maximum │ average │
├─────────┼─────────┼─────────┤
│ 1       │ 5       │ 3.13    │
└─────────┴─────────┴─────────┘
```

An aggregate function cannot be nested directly; `AVG(COUNT(*))` is invalid. A derived
table turns the inner computation into a table, which opens the way for the outer
computation. Giving the derived table an **alias** (here `tally`) is required, because the
outer query refers to it by name.

The second use is a table subquery appearing as a **list** in a filter:
`WHERE id IN (SELECT …)`. This phrasing reads well, but it behaves incorrectly, and
silently, once a null value gets mixed into the list.

## A Null Value Emptying the List

Suppose lost-item reports at the library are kept in a separate table. If a report has
not yet identified which book it belongs to, its `book_id` column is left null when the
row is recorded. Both "books with a lost report" and "books without one" can be written
with `IN`:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, genre TEXT);
INSERT INTO book VALUES (1,'Lost Time','fiction'),(2,'Silent House','fiction'),
                         (3,'Number Theory','science'),(4,'The Structure of the Universe','science');
CREATE TABLE lost(id INTEGER PRIMARY KEY, book_id INT, reported TEXT);
INSERT INTO lost VALUES (1,2,'2024-03-08'),(2,NULL,'2024-03-19');

SELECT title FROM book WHERE id IN (SELECT book_id FROM lost);
SELECT title FROM book WHERE id NOT IN (SELECT book_id FROM lost);
SQL
```

```text
┌──────────────┐
│    title     │
├──────────────┤
│ Silent House │
└──────────────┘
```

The first query gave one row, as expected. The second query gave **no rows** at all,
which is why only one table appears in the output. Yet three books have no lost report.

The cause is the three-valued logic introduced in the Data Modeling and Relational Theory
course. The expression `x NOT IN (a, b)` means `x <> a AND x <> b`. If the list contains a
null value, that comparison is neither true nor false — it becomes **unknown**, and an
unknown factor in a conjunction pulls the whole result to unknown, so the unknown row does
not pass the filter:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, genre TEXT);
INSERT INTO book VALUES (1,'Lost Time','fiction'),(2,'Silent House','fiction'),
                         (3,'Number Theory','science'),(4,'The Structure of the Universe','science');
CREATE TABLE lost(id INTEGER PRIMARY KEY, book_id INT, reported TEXT);
INSERT INTO lost VALUES (1,2,'2024-03-08'),(2,NULL,'2024-03-19');

SELECT 3 IN (2, NULL) AS "3 IN (2,NULL)", 3 NOT IN (2, NULL) AS "3 NOT IN (2,NULL)";
SELECT title FROM book b
WHERE NOT EXISTS (SELECT 1 FROM lost y WHERE y.book_id = b.id);
SQL
```

```text
┌───────────────┬───────────────────┐
│ 3 IN (2,NULL) │ 3 NOT IN (2,NULL) │
├───────────────┼───────────────────┤
│               │                   │
└───────────────┴───────────────────┘
┌───────────────────────────────┐
│             title             │
├───────────────────────────────┤
│ Lost Time                     │
│ Number Theory                 │
│ The Structure of the Universe │
└───────────────────────────────┘
```

The two blank cells in the first row show that both expressions produced a null value:
neither true nor false. `EXISTS`, in contrast, asks a different question — "**does** a
matching row exist" — and its answer is always true or false; it never produces a null
value. This is why `NOT EXISTS` returned all three books.

As a rule: if the column a subquery returns can hold a null value, `NOT IN` is replaced
with `NOT EXISTS`, or a `WHERE book_id IS NOT NULL` condition is added to the subquery. If
the schema guarantees the column cannot be null, `NOT IN` is safe.

## Where a Subquery Can Be Written

There are four positions where a subquery can be written, and each has a typical job:

- In the `SELECT` list: producing one additional value per row.
- Inside `FROM`: turning an intermediate result into a table.
- Inside `WHERE`: filtering rows against another query's result.
- Inside `HAVING`: filtering groups against another query's result.

A subquery in the `SELECT` list reads well, but it hides its cost:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch_id INT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice',4),(2,'Ben',4),(3,'Clara',5),(4,'Derek',5);
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (4,7,1,'2024-03-11',NULL),(6,2,2,'2024-03-01','2024-03-12'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10');

SELECT m.name,
       (SELECT COUNT(*) FROM loan l WHERE l.member_id = m.id) AS total,
       (SELECT MAX(pickup) FROM loan) AS last_transaction_date
FROM member m
ORDER BY m.id;
SQL
```

```text
┌───────┬───────┬───────────────────────┐
│ name  │ total │ last_transaction_date │
├───────┼───────┼───────────────────────┤
│ Alice │ 3     │ 2024-03-21            │
│ Ben   │ 2     │ 2024-03-21            │
│ Clara │ 1     │ 2024-03-21            │
│ Derek │ 0     │ 2024-03-21            │
└───────┴───────┴───────────────────────┘
```

The two columns in the output look alike, but their structure differs. The subquery in
the third column does not depend on the outer query; it is computed once and copied to
every row. The subquery in the second column depends on the outer row through `m.id`: it
has to be evaluated separately for every member. This link multiplies the subquery's cost
by the number of rows.

A side benefit of this phrasing also becomes visible: `Derek`, who has no loan record at
all, gets a zero. Written with an inner join, this member would drop out of the result
entirely, and getting the member back would require an outer join; the subquery produced
a zero when there was no match.

## Summary

- A subquery is classified as scalar, row, or table by the shape it returns; the shape
  determines where it can be written.
- A scalar subquery must return at most one row; zero rows produce a null value.
- A derived table inside `FROM` is the standard way to layer aggregate functions, and it
  must be given an alias.
- If the subquery's column can hold a null value, `NOT IN` silently returns an empty
  result; `NOT EXISTS` gives the correct answer because it is unaffected by three-valued
  logic.
- A subquery that does not reference a column of the outer query is evaluated once; one
  that does is evaluated per row.

## Next Step

The last example in this lesson placed two subqueries side by side: one evaluated once,
the other evaluated per row. In the phrasing, the difference was a single column
reference, but the difference in the amount of work is measured by a multiplying factor.
The next lesson defines this second form — the one tied to the outer row, the correlated
subquery — measures it by counting how many times it is evaluated, and compares it against
an equivalent join-based phrasing.