Subqueries, and when they are secretly a loop

A scalar subquery returns one value. No rows makes it null; more than one row makes it an error, which is the right outcome — a query that asked a question with two answers should say so rather than silently pick one.

SELECT (SELECT max(total) FROM orders) AS biggest, (SELECT total FROM orders WHERE id = -1) AS missing

Correlated, and what it costs

A subquery that references a column from the enclosing query cannot be computed once, because its answer depends on which outer row is asking. So it runs again for every one of them. That is an N+1, written in one statement instead of a loop, and it is invisible in the SQL — you can only see it in the plan.

The uncorrelated version costs one execution however many rows probe it. Same shape, same length, entirely different cost.

EXISTS asks a question that always has an answer

EXISTS is the one test in SQL that never returns unknown: either a row was found or it was not, whatever nulls it contained. That is why NOT EXISTS is the safe way to write “none of these” and NOT IN is not.

SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.shipped) ORDER BY c.name LIMIT 5

Most correlated subqueries can be written as joins, and most of the time the planner will treat them identically. Write whichever reads more clearly — and then look at the plan, because “the planner will handle it” is a belief, and the plan is a fact.