Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL Subqueries: Correlated vs Uncorrelated, EXISTS, IN, and Performance

What makes a subquery run once versus once per row, why NOT IN returns nothing when the inner column has a NULL, and when IN beats a join.

~5 min readSQL
#SQL#Query Optimization#Performance
3D illustration of an inner nested query loop validating outer query rows with green boolean EXISTS beacon scanners

Quick Summary

A subquery is a SELECT inside another statement. It is uncorrelated when it can run once on its own, and correlated when it references a column from the outer query and therefore has to run again for each outer row. That single distinction decides most of the performance, and modern planners usually rewrite the correlated version into a semi-join anyway — but not always, and never for the cases where the subquery sits in the SELECT list.

EXISTS asks "is there at least one row?", stops at the first match, and is null-safe by construction. IN compares values, materialises a list, and breaks on NULL. For existence checks EXISTS is the safer default; for value lists IN is clearer. NOT IN against a nullable column is the classic bug that returns zero rows.

Basic syntax and query signatures

sql
-- Uncorrelated scalar subquery: runs once SELECT (SELECT COUNT(*) FROM orders WHERE status = 'paid') AS paid_orders; -- Correlated scalar subquery: conceptually once per outer row SELECT c.id, (SELECT MAX(o.placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order FROM customers c; -- Semi-join: keep customers with at least one matching row SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); -- Anti-join SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Also in the family: IN (subquery) and NOT IN (subquery), quantified comparisons (> ALL, = ANY), derived tables in FROM, and LATERAL/CROSS APPLY where the subquery needs to see the row to its left. IN with a subquery is a semi-join too; the planner is free to turn it into a hash semi-join.

Scalar subqueries must return at most one row and one column. That is enforced at runtime, not at parse time — which is why the error appears in production the week the data grows a duplicate.

Key comparison table

FormReturnsNULL behaviourPlanner equivalentRuns
EXISTS (subquery)BooleanSafe — NULL rows still "exist"Semi-join / nested loopOnce per outer row at worst, short-circuits
IN (subquery)BooleanNULL in list makes non-matches UNKNOWNSemi-join / hashed subplanOnce if uncorrelated
NOT IN (subquery)BooleanAny NULL → no rows at allAnti-join (rarely)Same as above
NOT EXISTSBooleanSafeAnti-joinShort-circuits per row
Scalar subqueryOne valueNULL if no rowProjection / lateralOnce per outer row if correlated
Derived table (FROM (SELECT …))RowsNormalInlined where possibleOnce
RewriteWhen it helpsWhen it does not
EXISTS → JOIN + DISTINCTOuter rows duplicate, and you want other columns from the inner tableDuplicates are likely; DISTINCT sorts the whole result
IN → JOINValues needed from both sidesSemantics change: fan-out, and NULL handling differs
Correlated SELECT → LEFT JOIN aggregateMany outer rows, index on the join keyInner aggregate is expensive and few outer rows qualify
Correlated → window functionRanking or "latest per group"Filtering inside WHERE requires a CTE anyway

Practical edge cases and deep dives

1. NOT IN and the NULL that empties the result

sql
-- Returns zero rows if any customers.id is NULL in the subquery result SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

The expression becomes id <> 1 AND id <> 2 AND id <> NULL, and the last term is UNKNOWN, so the whole conjunction is never TRUE. Even a single NULL in the subquery result — one order with no customer — silently empties the output. NOT EXISTS has no such behaviour:

sql
SELECT c.* FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

The other fix, NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL), works but relies on every future edit remembering the guard. NOT EXISTS also handles composite keys and correlated conditions that IN cannot express.

2. Correlated subqueries in the SELECT list are per-row work

sql
SELECT c.id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders, (SELECT MAX(o.total) FROM orders o WHERE o.customer_id = c.id) AS biggest FROM customers c;

With 200,000 customers this is 400,000 index lookups — the SQL version of an N+1 query. It is correct, and on a small table it is perfectly fine. On a large one, two LEFT JOINs against one aggregate pass do the same work once:

sql
WITH stats AS ( SELECT customer_id, COUNT(*) AS orders, MAX(total) AS biggest FROM orders GROUP BY customer_id ) SELECT c.id, COALESCE(s.orders, 0) AS orders, s.biggest FROM customers c LEFT JOIN stats s ON s.customer_id = c.id;

The rewrite changes one thing besides speed: with no orders, the subquery returns 0 for COUNT and NULL for MAX, while the join returns 0 and NULL too — but only because of the COALESCE. Without it the join version returns NULL for the count, which is a different report.

3. EXISTS and short-circuiting

sql
SELECT p.* FROM posts p WHERE EXISTS ( SELECT 1 FROM comments c WHERE c.post_id = p.id AND c.created_at > now() - interval '7 days' );

EXISTS does not care what the subquery selects — SELECT 1 is idiomatic, SELECT * costs the same, SELECT COUNT(*) does not (the aggregate forces a full count and defeats the early exit). With an index on comments (post_id, created_at) the planner can stop at the first matching row per post, which is what makes this cheap on a comment table with millions of rows.

The correlated form also wins for "exists with a limit-shaped condition": WHERE (SELECT count(*) FROM comments c WHERE c.post_id = p.id) > 0 computes every count and then compares, while EXISTS stops at one.

4. Scalar subqueries that return more than one row

sql
-- ERROR: more than one row returned by a subquery used as an expression (21000) SELECT id, (SELECT total FROM orders WHERE customer_id = c.id) FROM customers c; -- Correct: pick a row explicitly SELECT id, (SELECT o.total FROM orders o WHERE o.customer_id = c.id ORDER BY o.placed_at DESC, o.id DESC LIMIT 1) AS latest_total FROM customers c;

The LIMIT 1 does two things: it removes the runtime error, and it documents which row you meant. When the intent is "all of them", the scalar form is the wrong shape and a JOIN or LATERAL is the right one. MySQL reports the same condition as error 1242, SQL Server as 512 (Subquery returned more than 1 value).

5. When IN is genuinely faster

sql
SELECT * FROM posts WHERE author_id IN (SELECT id FROM users WHERE is_editor);

Uncorrelated IN can be planned as a hashed subplan: the inner query runs once, the values go into a hash table, and each outer row probes it. That is linear work with no per-row round trip, and for a small inner set it beats a join because there is no fan-out to deduplicate. The correlated EXISTS version is planned as a nested loop and runs once per outer row.

The trade flips when the inner set is huge: hashing a million ids costs memory, and a merge or nested-loop semi-join with an index may win. EXPLAIN ANALYZE on both forms against production-sized data is the only reliable answer; the rule of thumb is that uncorrelated IN is fine for small lists, EXISTS is fine for indexed existence, and joins are fine when you need columns from both sides.

Common mistakes and runtime errors

Error or symptomCauseFix
more than one row returned by a subquery used as an expression (PG 21000)Scalar subquery matched multiple rowsAdd LIMIT 1 with a deterministic ORDER BY, or use a join
Subquery returns more than 1 row (MySQL 1242 / SQL Server 512)SameSame
Query returns zero rows only in productionNOT IN with a NULL in the inner resultNOT EXISTS
operator does not exist: integer = textType mismatch between subquery and outer columnCast explicitly and fix the schema
Slow query with high loops in EXPLAINCorrelated subquery executed per rowRewrite as join against a pre-aggregated CTE
subquery in FROM must have an alias (PostgreSQL)Derived table without a nameFROM (SELECT ...) AS t
invalid reference to FROM-clause entry for table "x"Correlated reference where correlation is not allowed, or alias shadowingQualify the outer alias; add LATERAL
LIMIT/ORDER BY inside IN (subquery) has no effectORDER BY in a subquery used as a setMove ordering to the outer query
Two DISTINCT aggregates in one subqueryCOUNT(DISTINCT a), COUNT(DISTINCT b) fan-outSplit into separate LEFT JOINs
Same answer, ten times slower after rewriteJOIN introduced fan-out and DISTINCTPre-aggregate, or keep the semi-join

The practical rule: use EXISTS/NOT EXISTS for existence, IN for small uncorrelated value lists, and joins when you need the other table's columns. Read the plan with loops visible — a node executed once per outer row is the signature of a correlated subquery that should have been a join.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.