Subqueries: a query inside a query

Four shapes, four uses

A subquery is a SELECT inside another statement. It appears in four positions, and they behave differently enough to be worth separating.

Scalar, in the SELECT list. Returns exactly one row and one column, becoming a value on each output row:

SELECT o.id, o.amount,
       (SELECT full_name FROM customers c WHERE c.id = o.customer_id) AS customer
FROM orders o;

It cannot change the grain, which is its great virtue. If it ever returns two rows, the query fails rather than silently duplicating: a much better failure than a join’s.

In the FROM clause, as a derived table. It must have an alias:

SELECT city, avg(order_count) FROM (
  SELECT customer_id, city, count(*) AS order_count
  FROM orders GROUP BY customer_id, city
) per_customer
GROUP BY city;

This is how you aggregate an aggregate: the average number of orders per customer, by city, which no single GROUP BY can express.

In WHERE, with IN or a comparison. Filters against a list computed elsewhere:

SELECT * FROM students
WHERE id IN (SELECT student_id FROM payments WHERE amount > 20000);

Correlated, referencing the outer query. The subquery mentions a column from outside, so conceptually it runs once per outer row:

SELECT * FROM orders o
WHERE o.amount > (SELECT avg(amount) FROM orders WHERE customer_id = o.customer_id);

“Orders larger than that customer’s own average.” The o.customer_id reference is what makes it correlated.

EXISTS, and why it is the one to reach for

SELECT s.* FROM students s
WHERE EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);

EXISTS asks a yes/no question and stops at the first matching row. SELECT 1 is conventional: the columns are never read, so people write the cheapest thing possible.

Three reasons to prefer it over the alternatives.

It cannot multiply rows. A join to payments would return one row per payment; EXISTS returns one row per student regardless of how many payments there are. When the question is “which students have paid” rather than “what did they pay”, this is exactly right and no DISTINCT is needed.

It handles NULL correctly. NOT IN against a list containing a NULL returns zero rows, always, as the NULL lesson shows in detail. NOT EXISTS has no such trap.

It stops early. A student with 400 payments is settled by the first one.

IN versus EXISTS versus JOIN

For a simple membership test on modern Postgres, all three are usually rewritten into the same plan, and the choice is about readability. The differences that survive:

• Use EXISTS when you want one row per outer row and only care whether a match exists.
• Use JOIN when you need columns from the other table in the output.
• Use IN when the list is small and literal, or comes from a simple subquery, and you find it clearer.
• Never use NOT IN against a subquery on a nullable column.

That last one is worth writing on a wall. NOT IN (SELECT student_id FROM payments) returns nothing at all if a single student_id is NULL, and returning nothing looks like a valid finding.

The performance shape to know

A correlated subquery can run once per outer row. Ten thousand outer rows and a subquery costing 2 ms is twenty seconds. Postgres often decorrelates these into a single join or hash aggregate, and then the cost disappears; but “often” is not “always”, and other engines are less reliable about it.

The diagnostic is direct: run EXPLAIN ANALYZE and look for a node with loops=10000. That number is telling you the subquery ran ten thousand times. When you see it and the query is slow, rewrite the correlated form as a pre-aggregated CTE joined once:

WITH avg_per_customer AS (
  SELECT customer_id, avg(amount) AS avg_amount FROM orders GROUP BY customer_id
)
SELECT o.* FROM orders o
JOIN avg_per_customer a ON a.customer_id = o.customer_id
WHERE o.amount > a.avg_amount;

One pass to build the averages, one join to use them. Same answer, and it stays fast as the table grows.

Readability

Subqueries nested three deep are the hardest SQL to read, because you have to hold the inner result in your head while parsing the outer one. Beyond one level of nesting, name the pieces with CTEs, the next lesson. A query built from four named steps is understandable by somebody who has never seen it; the same logic nested four deep is not.