Subqueries — correlated vs uncorrelated: Question bank

Visual warm-up — see the two behaviours

Before the traps, picture what each subquery kind actually does at runtime. The picture below contrasts the uncorrelated flow (compute once, plug in) with the correlated flow (borrow a value from each outer row, re-evaluate).

Figure — Subqueries — correlated vs uncorrelated

And here is the same split laid out as a lookup table so you can place any subquery by its context (SELECT / WHERE / FROM) and its correlation:

Recall One-line recall of the table

Context (SELECT/WHERE/FROM) is independent of correlation ::: True — a subquery in any clause can be correlated or not; only the presence of an outer-column reference decides.

True or false — justify

A subquery that references an outer column is correlated.

True — the defining test is whether the inner query mentions a column/alias belonging to the outer query; that reference ("correlation") forces re-evaluation per outer row.

An uncorrelated subquery can be copy-pasted and run on its own without error.

True — because it references nothing from the outside, it is self-contained; if it errored standalone ("unknown column o.id"), it would be correlated instead.

Correlated subqueries are always slower than uncorrelated ones.

False — that is only the logical semantics; optimizers routinely rewrite correlation into a join or semi-join, so real runtime can match an uncorrelated query. Read the execution plan before claiming slowness.

Using EXISTS instead of IN changes the result of an existence check.

Usually false for existence, but it can differ with NULLs — IN with a NULL in the list can yield NULL/unknown and drop rows, while EXISTS only cares whether a matching row exists. See NULL handling in SQL.

A subquery in the FROM clause (a derived table) is a third kind, separate from correlated/uncorrelated.

False — placement (FROM, WHERE, SELECT) is orthogonal; the correlated/uncorrelated split is purely about whether it references the outer query. A plain derived table is usually uncorrelated, but a LATERAL (Postgres) / CROSS APPLY (SQL Server) derived table can reference the outer row and is then correlated.

Given SELECT AVG(e2.salary) FROM employee e2 WHERE e2.dept_id = e.dept_id sitting inside an outer ... FROM employee e, this aggregate subquery is uncorrelated because it uses AVG.

False — AVG is irrelevant to correlation; here e2 is the inner alias for employee and e is the outer alias, so the filter e2.dept_id = e.dept_id borrows the outer row's e.dept_id, making the whole subquery correlated.

If a correlated subquery returns no rows, EXISTS around it evaluates to false.

True — EXISTS asks "is there at least one row?"; zero rows means no, so the outer row is filtered out. This is exactly why EXISTS handles emptiness cleanly.

Every correlated subquery can be rewritten as a join with identical results.

False in general — semi-join semantics (EXISTS) match, but a correlated subquery can silently duplicate or de-duplicate rows differently than a naive inner join. Anti-joins (NOT EXISTS) especially need care to preserve meaning.

Spot the error

SELECT name FROM employee WHERE salary > AVG(salary); — what's wrong?

WHERE runs per-row before aggregation, so a bare AVG cannot appear there; you must compute the average in an (uncorrelated) subquery first, then compare. See Aggregate Functions GROUP BY.

SELECT c.name FROM customer c WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = id); — the intent was "customers with an order," but why is this fragile?

Nothing qualifies customer_id or id, so both may resolve to the orders table (or ambiguously), breaking the intended correlation to c.id. Always alias and qualify: o.customer_id = c.id.

SELECT name FROM customer WHERE id NOT IN (SELECT customer_id FROM orders); — why can this return zero rows unexpectedly?

If any customer_id is NULL, NOT IN compares against NULL, yielding unknown for every row, so nothing passes. Use NOT EXISTS instead — it is NULL-safe.

SELECT e.name FROM employee e WHERE e.salary > (SELECT AVG(e2.salary) FROM employee e2); — is this the per-department query the author wanted?

No — the inner query names alias e2 but never links it to the outer e (no WHERE e2.dept_id = e.dept_id), so it computes the global average (uncorrelated). To get each employee's own department average, add that correlating filter to e.dept_id.

SELECT name, (SELECT dept_name FROM dept) FROM employee e; — why might this error?

The uncorrelated scalar subquery over all of dept returns many rows, but a scalar position demands exactly one; either correlate it (SELECT dept_name FROM dept d WHERE d.id = e.dept_id) or aggregate it to one value.

SELECT name FROM customer c WHERE c.id IN (SELECT o.customer_id FROM orders o WHERE o.customer_id = c.id); — is this a logic error or just poor style, and why?

It is not a logic error — it returns the correct customers-with-orders set; but it is stylistically redundant because the correlated filter o.customer_id = c.id already restricts the inner result to this customer, so the outer IN test is guaranteed to match whenever the inner row exists. Engine-wise it forces a per-row correlated scan whose output is then re-membership-tested, work the planner may or may not simplify; a plain correlated EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) states the same intent with one clear check.

Why questions

Why does the question you're asking decide correlated vs uncorrelated, not personal taste?

If the threshold/answer is a single global fact ("company average"), it's uncorrelated; if it varies per outer row ("their own department average"), the inner query must borrow that row's value — forcing correlation.

Why can EXISTS be faster than IN even though both check existence?

EXISTS short-circuits at the first matching row and returns true immediately, whereas IN may materialize the full list; on large tables the early exit saves work. See EXISTS vs IN vs ANY.

Why is the naive correlated cost

N \cdot c

but the uncorrelated cost only

c

The uncorrelated inner query is a constant computed once; the correlated one changes with each outer row's borrowed value, so semantically it re-runs for all

N

rows — unless the optimizer rewrites it.

Why does WHERE needing an aggregate force a subquery in the first place?

WHERE filters individual rows before grouping, so it cannot see an aggregate over the set; the subquery computes the aggregate in a separate, earlier step and hands back a value to compare.

Why might two logically-equivalent queries (correlated subquery vs join) show completely different execution plans?

The optimizer chooses physical strategies (nested loop, hash semi-join, index seek) based on statistics and available indexes; equivalent logic doesn't guarantee equivalent plans — always inspect them via Query Optimizer Execution Plans.

Why do people wrongly call correlated subqueries "loops"?

The semantic description ("evaluate once per outer row") reads like a for-loop, but that is a mental model, not the runtime; the engine often flattens it into a set-based join.

Why does a LATERAL / CROSS APPLY derived table make a FROM-clause subquery correlated when an ordinary derived table cannot be?

An ordinary derived table is evaluated independently and joined afterward, so it cannot see outer rows; LATERAL/APPLY explicitly permits the inner subquery to reference columns from tables listed to its left, letting it produce a row-dependent result set — that outer reference is precisely what correlation means.

Edge cases

An uncorrelated scalar subquery (SELECT MAX(x) FROM t) on an empty table — what does it return?

NULL, not an error — an aggregate over zero rows yields NULL, and comparisons like salary > NULL then evaluate to unknown, quietly excluding every outer row.

A correlated EXISTS where the outer row's correlated value is NULL (e.g. o.dept_id = e.dept_id with e.dept_id NULL) — does it match?

No — NULL = anything is unknown, never true, so EXISTS finds no matching row and treats that outer row as "no match." See NULL handling in SQL.

An uncorrelated IN list that contains a NULL — how does membership behave?

x IN (1, 2, NULL) is true if x matches a non-null; if it matches none, the NULL makes the result unknown (not false), which matters mostly for the negated NOT IN form.

A correlated subquery that references the same table as the outer (self-correlation, like employee e vs employee e2) — is that legal?

Yes — the two aliases e (outer) and e2 (inner) distinguish two logical copies of employee, so e2.dept_id = e.dept_id compares each row against its peers; this is the standard per-group-aggregate pattern.

A LATERAL subquery like FROM employee e, LATERAL (SELECT AVG(e2.salary) AS avg_sal FROM employee e2 WHERE e2.dept_id = e.dept_id) AS d — correlated or not, and what does it produce?

Correlated — the inner block reads e.dept_id from the outer row, so it yields a per-employee avg_sal column you can then compare against, exactly like a correlated WHERE subquery but usable as a table.

A subquery in the SELECT list returning more than one row — outcome?

Runtime error in a scalar context ("subquery returns more than 1 row"); a SELECT-list scalar subquery must be guaranteed single-valued, typically by correlation to a key or by aggregation.

NOT EXISTS with an empty inner result — what happens?

It evaluates to true (there is indeed no matching row), so the outer row is kept — the mirror image of EXISTS on empty, and the NULL-safe way to express "has no orders."

Connections

Subqueries — correlated vs uncorrelated — the parent topic these traps drill into.
EXISTS vs IN vs ANY — the operators behind most existence-check traps here.
NULL handling in SQL — root cause of the NOT IN and correlated-NULL surprises.
Joins — inner outer cross — what correlated subqueries often become after rewriting.
Aggregate Functions GROUP BY — why WHERE salary > AVG(...) needs a subquery.
Query Optimizer Execution Plans — the truth-teller for any performance claim.