Part 2 — Querying › Chapter 11

Subqueries: EXISTS, IN and the correlated one

Three ways to ask the same question, one of which is a trap the moment a NULL appears.

From the advert

“Ottima conoscenza del linguaggio SQL” — knowing which of three equivalent forms to reach for, and which one has a null bug in it.

Four kinds of subquery

Kind	Returns	Where it goes
Scalar	Exactly one row, one column	Anywhere a value goes: `SELECT`, `WHERE`
Row / column	One column, many rows	`IN`, `ANY`, `ALL`
Derived table	A whole table	`FROM`. Must be aliased
Correlated	Depends on the outer row	`WHERE EXISTS`, `SELECT`, `LATERAL`

🧒 Simple

A normal subquery is a question you answer once and write on a sticky note. A correlated subquery is a question you have to answer again for every single row — “for this customer, is there an order?” — so it is like looking something up once per line of a list rather than once for the whole list.

EXISTS, IN and JOIN: the same question, three ways

“Customers who have ordered”, three ways — two of which are the same question and one of which is not.

-- 1. SEMI-JOIN with EXISTS. No duplicates, and it stops at the first match found.
SELECT c.* FROM customers c                          -- only customer columns come back
WHERE EXISTS (SELECT 1 FROM orders o                 -- SELECT 1: the value is never read
              WHERE o.customer_id = c.id);           -- correlated to the outer row

-- 2. IN. Equivalent HERE, and dangerous in its negative form (see chapter 09).
SELECT c.* FROM customers c                          -- same output as version 1…
WHERE c.id IN (SELECT customer_id FROM orders);      -- …one value tested against a set

-- 3. JOIN. DIFFERENT: a customer with three orders comes back three times.
SELECT c.* FROM customers c                          -- the rows are MULTIPLIED by matches
JOIN orders o ON o.customer_id = c.id;               -- needs DISTINCT to match the others

Why version 3 is not the same query

A join pairs rows, so the customer is repeated once per matching order. A semi-join tests for a partner and returns the left row once. Adding DISTINCT makes the results match, and makes the engine sort or hash the whole thing to do it.
EXISTS can short-circuit: the engine may stop reading a customer's orders at the first one. IN with a subquery is usually optimised into the same semi-join, but the negative forms are not equivalent — NOT IN collapses on a single NULL.
SELECT * here is fine because it means “the customer row” in an example. In production code name the columns: * changes meaning when the table does.

🎓 Which one, and the honest answer about performance

EXISTS is the default for “does a match exist?”. It is null-safe, cannot duplicate rows, and can short-circuit at the first match.
IN reads better with a literal list. With a subquery it is fine in the positive form and a landmine in the negative (chapter 09).
JOIN when you actually need columns from the other table.

“EXISTS is faster than IN” is folklore

Modern optimisers rewrite all three into the same internal semi-join and produce identical plans in the common cases. Saying “EXISTS is faster” in an interview is a small trap; saying “they usually plan identically, so I choose on semantics — and I avoid NOT IN because of nulls” is the answer that shows you have checked rather than repeated.

Correlated subqueries, and when they hurt

A correlated scalar subquery: one value, computed once per outer row.

-- The most recent order date per customer, as a correlated scalar subquery.
SELECT c.name,                                        -- one row per customer
       (SELECT MAX(o.placed_at)                       -- a SCALAR subquery: exactly one value
        FROM orders o                          -- the child table, read per outer row
        WHERE o.customer_id = c.id) AS last_order     -- c.id makes it CORRELATED
FROM customers c;                                     -- the outer query drives the loop

What “correlated” costs, and what it returns

Correlated means the subquery mentions a column from the outer query, so it cannot be evaluated once — logically it runs per outer row. A good optimiser rewrites it into a join or an index seek; a bad plan runs it a hundred thousand times.
A scalar subquery must return at most one row and one column. More rows is a runtime error; no rows is NULL, not an error — which is why a customer with no orders shows an empty last_order rather than breaking the query.
MAX(placed_at) over no rows is NULL for the same reason. Every aggregate except COUNT behaves this way.

Logically that runs once per customer. In practice a good optimiser will often decorrelate it into a grouped join — but not always, and not when the subquery contains something it cannot transform (a scalar UDF, a TOP with a tie-break, an ORDER BY). When it cannot, you have written an N+1 inside the database.

⚠ Trap — the same subquery three times

This shape is extremely common and is three correlated executions per row:

The anti-pattern this chapter is really about: the same subquery, three times, per row.

SELECT c.name,                                                          -- one row per customer
  (SELECT COUNT(*)         FROM orders o WHERE o.customer_id = c.id) AS n,      -- pass 1
  (SELECT SUM(total_cents) FROM orders o WHERE o.customer_id = c.id) AS spend,  -- pass 2
  (SELECT MAX(placed_at)   FROM orders o WHERE o.customer_id = c.id) AS last    -- pass 3
FROM customers c;                                                       -- × every customer

Why this is the classic “optimise this query” exercise

Three correlated subqueries over the same rows means the engine visits a customer's orders three times. Ten thousand customers, three lookups each: thirty thousand index seeks where one grouped pass would do.
It is also fragile: the three WHERE clauses must stay identical. Add AND status <> 'cancelled' to two of them and the report silently reports three different populations in three columns.
The fix is mechanical — aggregate once in a derived table and join to it — which is the next example.

Rewrite as one pre-aggregated join — one pass over orders instead of three per customer. This is a favourite “optimise this query” exercise in screening tests precisely because the fix is mechanical once you see it.

The same report, aggregated once: three columns, one pass over orders.

SELECT c.name,                                    -- still one row per customer
       COALESCE(o.n, 0)     AS n,                 -- no orders → NULL from the LEFT JOIN → 0
       COALESCE(o.spend, 0) AS spend,             -- same treatment for the money column
       o.last                                     -- left as NULL: "never ordered" is not a date
FROM customers c                                  -- the driving table: one row each
LEFT JOIN (                                       -- a derived table, evaluated ONCE
  SELECT customer_id,                             -- the join key
         COUNT(*)         AS n,                   -- all three aggregates…
         SUM(total_cents) AS spend,               -- …computed in the same pass…
         MAX(placed_at)   AS last                 -- …over the same grouped rows
  FROM orders GROUP BY customer_id                -- one row per customer, by construction
) o ON o.customer_id = c.id;                      -- so the join cannot multiply rows

What changed, in one sentence each

Three correlated executions per row became one grouped scan plus a join. On the reference database it is the difference between thousands of seeks and one pass.
LEFT JOIN and not JOIN: a customer with no orders must still appear. That choice is what makes the COALESCE calls necessary.
COALESCE(o.n, 0) — the padded side is NULL, and a count of nothing should read as 0. o.last keeps its NULL deliberately: there is no honest date to show.

LATERAL and APPLY: a join that can see the left row

🎓 The top-N-per-group tool

A normal derived table in FROM cannot reference columns from tables to its left. LATERAL (standard, PostgreSQL, Oracle 12c+, MySQL 8.0.14+) and CROSS APPLY / OUTER APPLY (SQL Server) lift that restriction. That makes “the three most recent orders for each customer” expressible directly — and it is often faster than the window-function version, because it can stop after three rows per customer instead of ranking all of them.

LATERAL and CROSS APPLY: a subquery that runs once per row and can return several.

-- PostgreSQL / MySQL 8 / Oracle
SELECT c.name, o.id, o.placed_at            -- columns from BOTH sides, unlike a scalar subquery
FROM customers c                            -- the driving table
CROSS JOIN LATERAL (                        -- LATERAL: this subquery may see c                        -- LATERAL: this subquery may see c
  SELECT id, placed_at FROM orders          -- an ordinary query, run once per customer
  WHERE customer_id = c.id                  -- ← sees the outer row; illegal without LATERAL
  ORDER BY placed_at DESC LIMIT 3           -- three per customer, newest first
) o;                                        -- the alias is required

-- SQL Server: CROSS APPLY, and OUTER APPLY to keep customers with no orders.
SELECT c.name, o.id, o.placed_at            -- same shape, same meaning
FROM customers c                            -- again the driving table
CROSS APPLY (SELECT TOP (3) id, placed_at   -- TOP goes before the columns in T-SQL
             FROM orders                    -- the correlated side
             WHERE customer_id = c.id       -- the correlation, again
             ORDER BY placed_at DESC) o;    -- and the ordering that makes TOP meaningful

The two things worth remembering

A plain subquery in FROM is evaluated independently and cannot mention the tables beside it. LATERAL (standard, PostgreSQL) and APPLY (SQL Server) lift that restriction.
CROSS drops customers whose subquery returned nothing; OUTER keeps them with NULLs. It is exactly the inner/left distinction, with different keywords — and the same bug when you pick the wrong one.
For top-N per group this often beats a window function, because it can stop after three rows per customer instead of ranking every order and throwing most of them away.

CROSS vs OUTER

CROSS APPLY / CROSS JOIN LATERAL drops left rows whose subquery returned nothing — so customers with no orders disappear. OUTER APPLY / LEFT JOIN LATERAL … ON TRUE keeps them with nulls. Same relationship as inner and left joins, and the same bug when you pick the wrong one.

The same “latest order per customer”, ranked rather than sought — run both and compare the plans.

-- Window function: rank EVERYTHING, then filter.
SELECT * FROM (                                          -- the wrapper exists only to filter rn
  SELECT o.*,                                            -- every order column, plus…
         ROW_NUMBER() OVER (PARTITION BY customer_id     -- …a number restarting per customer
                            ORDER BY placed_at DESC) rn  -- newest first, so rn = 1 is the latest
  FROM orders o                                          -- every order is read and ranked
) t WHERE rn = 1;                                        -- WHERE cannot see rn one level up

-- LATERAL seeks the top row per customer instead. Usually faster when customers
-- are few and orders are many, and there is an index on (customer_id, placed_at).

Why the wrapper is not optional

Window functions are computed in step 5, with SELECT. WHERE ran in step 2, so WHERE rn = 1 in the same query is a syntax error — the column does not exist yet. The derived table or CTE gives it somewhere to exist.
ORDER BY placed_at DESC with no tie-breaker makes the result non-deterministic when two orders share a timestamp: either could be numbered 1. Adding , id DESC settles it — and interviewers listen for that.
Ranking reads every order. LATERAL/APPLY with a matching index reads three. Same answer, different cost, and the right choice depends on the shape of the data.

A scalar subquery must return one row and one column, or it is a runtime error — not a null.
EXISTS is the default for existence checks: null-safe, non-duplicating, short-circuiting.
EXISTS, IN and a de-duplicated join usually produce the same plan. Choose on meaning, not on folklore.
Never NOT IN over a nullable column. NOT EXISTS instead.
Three correlated subqueries over the same table is three passes. Pre-aggregate and join once.
A correlated subquery the optimiser cannot decorrelate is an N+1 inside the database.
LATERAL / APPLY lets a derived table see the outer row — the natural top-N-per-group tool.
CROSS APPLY drops rows with an empty subquery; OUTER APPLY keeps them.

Interview questions this chapter answers

Difference between EXISTS and IN?

Semantically, EXISTS asks whether any row matches and short-circuits; IN compares a value against a set. They plan the same way in most engines for the positive case. The real difference is negative: NOT IN returns nothing if the set contains a null, and NOT EXISTS does not.

Get the three most recent orders per customer.

Two good answers. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) filtered to <= 3, which is portable; or CROSS APPLY/LATERAL with ORDER BY … LIMIT 3, which can stop early per customer and is usually faster with the right index.

This query has four correlated subqueries and is slow. What do you do?

Collapse them into one pre-aggregated derived table joined once. Four correlated subqueries over the same child table can mean four executions per outer row; one GROUP BY plus a join is a single pass.