A Query Inside a Query: Subqueries and the NOT IN Trap

Scalar subqueries, derived tables, IN, EXISTS, and correlated subqueries — plus the one NULL that quietly makes NOT IN return nothing, and the NOT EXISTS that fixes it. Run against PostgreSQL 18.

Every query so far has been flat: name some tables, filter, group, done. But sometimes the value you want to filter by is itself the answer to a query. “Books priced above the average” needs the average first, and the average is a query. A subquery is a SELECT nested inside another statement. It turns “I need a number I don’t know yet” into a single sentence the database resolves in one pass.

There are a few shapes of subquery, and they behave differently enough to be worth taking one at a time. We’ll end on the one that bites everyone at least once: NOT IN against a set that hides a NULL.

Scalar subqueries: a query that returns one value

The simplest subquery returns exactly one row and one column, so it stands in wherever a single value is allowed. The average book price is one number:

SELECT round(avg(price), 2) AS avg_price FROM books;
 avg_price
-----------
     30.13

Drop that query into a WHERE clause and you can ask which books beat it, without ever typing 30.13 yourself:

SELECT count(*) AS above_avg
FROM books
WHERE price > (SELECT avg(price) FROM books);
 above_avg
-----------
       298

The inner SELECT runs, produces 30.13, and the outer query compares each book’s price against it. The point isn’t just brevity. If a price changes tomorrow the query stays correct, because it recomputes the average every time it runs. Hard-coding 30.13 would rot the moment the data moved.

A scalar subquery can also sit in the SELECT list, producing a computed column:

SELECT a.name,
       (SELECT count(*) FROM books b WHERE b.author_id = a.author_id) AS books
FROM authors a
ORDER BY books DESC
LIMIT 5;
   name    | books
-----------+-------
 Author 21 |    26
 Author 15 |    23
 Author 38 |    22
 Author 11 |    21
 Author 34 |    21

That inner query mentions a.author_id, a column from the outer query. That makes it a correlated subquery, which we’ll come back to. If a scalar subquery ever returns more than one row, Postgres raises an error rather than guessing which one you meant.

Subqueries in FROM: derived tables

A subquery can also stand in for a whole table in the FROM clause. The result set of the inner query becomes a temporary table, a derived table, that the outer query reads from. It must be given an alias.

Say you want per-genre averages, but only the genres that average above 30:

SELECT genre, avg_price
FROM (
    SELECT genre, round(avg(price), 2) AS avg_price
    FROM books
    GROUP BY genre
) AS g
WHERE avg_price > 30
ORDER BY avg_price DESC;
  genre   | avg_price
----------+-----------
 Sci-Fi   |     31.33
 History  |     31.05
 Children |     30.75

You cannot filter on avg_price in a plain WHERE — that’s what HAVING was for. But once the aggregation is wrapped in a derived table, avg_price is just an ordinary column of g, and the outer WHERE filters it like any other. Derived tables are how you stack one transformation on top of another.

IN and EXISTS: does the row have a match?

Often the question is membership: is this row’s key in some set of interest? Two tools answer that, and the difference between them matters more than it first appears.

IN compares a value against the list of values a subquery returns. How many books were written by authors from Japan?

SELECT count(*)
FROM books
WHERE author_id IN (SELECT author_id FROM authors WHERE country = 'Japan');
 count
-------
    71

EXISTS is subtly different. It doesn’t collect values; it asks whether the subquery produces any row at all, and stops at the first one. Because of that it’s usually written EXISTS (SELECT 1 FROM ...) — the 1 is a placeholder, since the actual value is never used. Which authors have at least one book?

SELECT count(*)
FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.author_id);
 count
-------
    40

All 40 authors do. The inner query references a.author_id, so it is correlated: it re-runs conceptually once per outer row, checking that this author has a book. EXISTS is the natural fit for “has a related row,” and it short-circuits, which can make it faster than the equivalent IN.

Correlated subqueries: once per outer row

A correlated subquery is one that depends on the current outer row, so it can’t be computed once up front. It runs, in the logical model, for every row the outer query considers. That makes it expressive and potentially expensive.

A good use: books priced above the average for their own genre, not the global average.

SELECT title, genre, price
FROM books b
WHERE price > (SELECT avg(price) FROM books b2 WHERE b2.genre = b.genre)
ORDER BY genre, price DESC
LIMIT 6;
     title      |  genre   | price
----------------+----------+-------
 Book Title 88  | Children | 54.57
 Book Title 484 | Children | 54.56
 Book Title 346 | Children | 53.92
 Book Title 166 | Children | 53.36
 Book Title 232 | Children | 53.19
 Book Title 292 | Children | 53.05

For each book, the inner query recomputes the average price of that book’s genre and compares. Across the whole table that’s 304 books above their genre line. The correlation (b2.genre = b.genre) is what makes “its own genre” possible in a single statement.

The anti-join, and the NOT IN trap

A common question is the negative one: which rows have no match? “Customers who have never had an order delivered.” All 5,000 of our customers have ordered something, but reaching delivered is a separate matter. NOT EXISTS states it directly — keep the outer row when the subquery finds nothing:

SELECT count(*) AS never_delivered
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id AND o.status = 'delivered');
 never_delivered
-----------------
              33

33 customers have placed orders but never seen one reach delivered. This shape (a SELECT 1 ... WHERE NOT EXISTS) is the anti-join, and it’s one of the most useful patterns in SQL.

The obvious-looking alternative is NOT IN, and on data with no NULLs it agrees:

SELECT count(*) AS never_delivered
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE status = 'delivered');
 never_delivered
-----------------
              33

Same 33 — because orders.customer_id is never NULL. Now watch what a single NULL does. Here I splice one into the subquery to simulate a nullable column:

SELECT count(*) AS never_delivered
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE status = 'delivered'
                          UNION ALL SELECT NULL);
 never_delivered
-----------------
               0

Zero. Every customer vanished, silently. You can see the mechanism stripped to its bones with an inline list:

SELECT 'row returned' AS result WHERE 3 NOT IN (1, 2, NULL);
 result
--------
(0 rows)

3 NOT IN (1, 2, NULL) expands to 3 <> 1 AND 3 <> 2 AND 3 <> NULL. The first two are TRUE, but 3 <> NULL is UNKNOWN, and TRUE AND TRUE AND UNKNOWN is UNKNOWN, never TRUE. So no row qualifies. NOT IN doesn’t error; it just returns nothing. That is the worst kind of bug: a query that runs green and answers wrong. Drop the NULL and the row returns:

SELECT 'row returned' AS result WHERE 3 NOT IN (1, 2);
    result
--------------
 row returned

NOT EXISTS is immune, because it tests for the presence of a matching row rather than comparing against a list of values. It stayed at 33 with the spliced NULL in play, and it stays there no matter how many NULLs hide in the subquery. The rule to carry: use NOT EXISTS for anti-joins, not NOT IN, unless you can guarantee the subquery column is never null. (MySQL and SQLite share this exact NULL behavior — it’s standard SQL, not a Postgres quirk.)

Final thoughts

A subquery lets one query supply a value, a table, or a yes/no to another. Scalar subqueries stand in for a single value; derived tables stand in for a whole table in FROM; IN and EXISTS answer membership; and correlated subqueries run per outer row when the inner question depends on the outer one. The one hazard worth memorizing is NOT IN over a nullable set: a lone NULL collapses the result to nothing, without complaint, and NOT EXISTS is the fix. Next we stop nesting queries and start stacking their results, combining whole result sets with the set operators.

Next: Stacking result sets — UNION, INTERSECT, and EXCEPT, and the rules that make two queries line up.

Comments