Subqueries

The idea

A subquery is just a query in parentheses. What matters is where you put it, because that decides what it is allowed to return. In a WHERE x = (…) slot it is a value, so it must produce exactly one row and one column. In an IN (…) slot it is a set, so a column of many rows is fine. In FROM it is a table, so it needs a name.

The interesting case is the correlated subquery: it mentions a column from the outer query, so it cannot be computed once. It re-runs for every outer row. EXISTS makes each of those runs cheap because it can stop at the first matching row. And very often the whole correlated shape is a join in disguise, which the database can do in one pass.

See it run

How it works

Read a subquery by asking two questions. What shape does this slot need? and does the inner query mention an outer column? The first answer tells you the legality rules; the second tells you how many times it runs.

-- 1. scalar: exactly one row, one column — it stands where a value stands
SELECT c.name,
       (SELECT max(o.amount) FROM orders o WHERE o.customer_id = c.id) AS biggest
FROM   customers c;

-- 2. set test: IN / ANY / ALL — the subquery returns one column, many rows
SELECT name FROM customers
WHERE  id IN (SELECT customer_id FROM orders WHERE amount >= 25);

-- 3. derived table: a query in FROM. It is a table, so it must have a name.
SELECT hits.customer_id, hits.n
FROM   (SELECT customer_id, count(*) AS n
        FROM   orders GROUP BY customer_id) AS hits   -- AS hits is required
WHERE  hits.n > 2;

-- 4. correlated + EXISTS: mentions c.id, so it re-runs per outer row,
--    but it may stop reading as soon as one row qualifies.
SELECT c.name
FROM   customers c
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE o.customer_id = c.id AND o.amount >= 25);

-- 4b. the same question as a semi-join: build the set once, probe once
SELECT c.name
FROM   customers c
JOIN   (SELECT DISTINCT customer_id FROM orders WHERE amount >= 25) AS hits
  ON   hits.customer_id = c.id;

Note what EXISTS actually asks: “is there at least one row?” The SELECT 1 is not special: the select list of an EXISTS subquery is never evaluated, so SELECT 1, SELECT * and SELECT o.id are identical in meaning.

One honest caveat: a modern planner will usually flatten a correlated EXISTS into a semi-join by itself, so the counter in the demo shows the naive nested-loop shape rather than a promise about your database. What it cannot always flatten is a scalar subquery in the select list, or count(*) > 0 hidden behind a comparison.

Cost

n = outer rows, m = inner rows

k is how far the scan gets before the first hit: that is the whole value of EXISTS. The trade-off is memory: a semi-join trades a hash table over the inner keys for the ability to answer every outer row from one pass.

Watch out for

• A scalar subquery that returns two rows is a runtime error, not a warning. SELECT (SELECT city FROM addresses a WHERE a.cust_id = c.id) … works fine until one customer gets a second address, then the query dies with “more than one row returned by a subquery used as an expression”. Zero rows is worse in a way: it quietly yields NULL, and every comparison against it becomes unknown.
• NOT IN plus a single NULL returns nothing, ever. x NOT IN (1, NULL) evaluates to unknown, not true, so the row is filtered out. If orders.customer_id is nullable, WHERE id NOT IN (SELECT customer_id FROM orders) silently returns zero rows. Use NOT EXISTS, which compares row by row and is null-safe here.
• An unqualified column inside the subquery binds outward if the inner table lacks it. DELETE FROM users WHERE id IN (SELECT id FROM stale_ids): if that table's column is called user_id, then id resolves to users.id, the condition is always true, and you delete the table. Always qualify: s.user_id.
• count(*) > 0 where you meant EXISTS. Counting must reach the end of the matching set; existence can stop at row one. Same result, very different work on a big child table, and the same applies to count(*) = 0 versus NOT EXISTS.
• Turning EXISTS into a plain join can duplicate outer rows. A customer with three qualifying orders comes back three times. Reaching for DISTINCT fixes the row count but can also mask a real duplicate elsewhere; prefer joining against a de-duplicated derived table, or leave EXISTS alone: it is a semi-join by definition.
• A subquery in FROM needs an alias. Postgres and MySQL both reject it outright (“subquery in FROM must have an alias”). Name it something meaningful; you will be referring to its columns.

Worked example

Take the demo's question: which customers have placed at least one order of $25 or more? Five customers, eight orders, threshold 25.

With correlated EXISTS, the outer query reads all five customers and re-runs the inner query five times. Ada matches on the very first order row, so that run reads one row. Bo has no orders at all, so his run reads all eight and returns false. Cy's qualifying order sits at position five, Dee's at position four, and Eli's only order is $10, so his run also reads all eight. That is 1 + 8 + 5 + 4 + 8 = 26 inner reads, plus 5 outer reads: five executions, 31 reads.

Swap EXISTS for count(*) > 0 and nothing can stop early: every run reads all eight orders, so it becomes five executions, 45 reads for the identical answer. Now rewrite it as a join against (SELECT DISTINCT customer_id FROM orders WHERE amount >= 25) AS hits. The inner query no longer mentions c.id, so it is evaluated once: eight order rows go in, the set {1, 4, 3} comes out, and then five customers are probed against it. One execution, 13 reads, and the result, Ada, Cy, Dee, is exactly the same. Slide the threshold and watch the early-exit position move; the gap between the three shapes is the lesson.

Check yourself