Subqueries — correlated vs uncorrelated: Visual walkthrough

See the parent: 4.4.7 · Subqueries — correlated vs uncorrelated.

Step 1 — What is a table, a row, and a query?

WHAT. Before any nesting, fix the vocabulary. A table is a grid: named columns across the top, and rows running down — each row is one record (one employee, one order). A query is a request: "show me the rows that satisfy some rule."

WHY. Every later idea ("borrows a column", "runs per row") is meaningless until you can point at a single row and a single column on a picture. We earn those words now.

PICTURE. In the figure, the employee table has columns name, salary, dept_id. One row is boxed in red — that is the row a query is currently looking at. Hold this image: "the current outer row" will always mean this red box moving down.

Step 2 — What does "a query inside a query" actually mean?

WHAT. A subquery is a second SELECT written inside the parentheses of a first one. The inside one is the inner query; the outside one is the outer query.

WHY. We need a mental picture of "inside" that is more than syntax. The right picture is a box within a box: the outer query is a big frame; the inner query is a smaller frame drawn inside it. Data can flow out of the inner box (its answer) and — crucially — sometimes into it (a borrowed value). Which direction flows decides everything.

PICTURE. Two nested rectangles. The outer frame is labelled SELECT ... FROM employee. The inner frame (drawn inside the outer's WHERE) is labelled SELECT AVG(salary) .... A red arrow shows the inner answer flowing up and out to the outer query.

Step 3 — Uncorrelated: the inner box is sealed, so it runs once

WHAT. Consider Worked Example 1 from the parent:

outer row’s value salary > inner box — no outer column inside (SELECT AVG(salary) FROM employee)

Term by term: on the left, salary is a value taken from the current red outer row. On the right, the whole parenthesis is the inner box. Look inside that box: it names only employee's own salary — nothing from the outer red row. The box is sealed.

WHY. Because nothing flows in, the inner box's answer cannot change as the red row moves. A quantity that never changes should be computed once. So the database computes AVG(salary) a single time — say $50000$ — and that constant is reused for every comparison.

PICTURE. The inner box glows red exactly once at the top (one evaluation → the number $50000$ ). Then the red outer row slides down the table, and each row compares against the same stored $50000$ . One evaluation, many cheap comparisons.

Step 4 — Correlated: a value flows IN, so the box must re-run

WHAT. Now Worked Example 4 — "above their own department's average":

e.salary > (SELECT AVG(e2.salary) FROM employee e2 WHERE inner table e2.dept_id = outer red row! e.dept_id)

Term by term: e2.dept_id belongs to the inner copy of the table. But e.dept_id on the right of that = belongs to the outer red row. A value from the current red row has leaked into the inner box.

WHY. The inner box now contains the outer alias e.dept_id. As the red row slides to a different department, the value flowing in changes, so the inner answer changes. A quantity that changes per row must be recomputed per row. That is the mechanical reason correlated means "once per outer row."

PICTURE. The red arrow now points downward, into the inner box (opposite of Step 2). As the red row moves — dept 7, then dept 3, then dept 7 again — the inner box re-glows red each time, producing a different threshold ( $60000$ , then $45000$ , ...).

Step 5 — The 5-second diagnostic, seen as "trace the arrow"

WHAT. The parent's diagnostic — "does the inner SELECT mention an outer column?" — is now a visual act, not a memorised rule.

WHY. You do not need to reason about performance to classify a subquery. You just look inside the inner box and ask: is there a name here that belongs to the outer red row? If yes, an arrow flows in → correlated. If no arrow → uncorrelated. This is why an uncorrelated inner query can be copy-pasted and run alone, while a correlated one errors with "unknown column e.dept_id" — it is missing the value that used to flow in.

PICTURE. Two inner boxes side by side. Left: no incoming arrow, a green "copy-paste OK" tag. Right: a red incoming arrow, a "won't run alone" tag. Same shape, one difference.

Recall Try the diagnostic yourself

WHERE id IN (SELECT customer_id FROM orders) — arrow in? ::: No — the inner query names only orders.customer_id, nothing from the outer row → uncorrelated. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) — arrow in? ::: Yes — c.id is the outer alias flowing into the inner box → correlated.

Step 6 — Edge case: what if the outer table is empty ( $N = 0$ )?

WHAT. Push the pictures to their limit. Let the outer query scan $N = 0$ rows.

WHY. A good derivation must survive its extremes. Plug $N = 0$ into both cost formulas: $uncorrelated = c + 0 \cdot k = c, correlated = 0 \cdot c + 0 \cdot k = 0.$ This is a real, visible twist: the uncorrelated subquery still runs once ( $c$ ) even with zero outer rows, because it is sealed and evaluated up front. The correlated one runs zero times — no red row ever exists to feed it a value. The pictures explain the arithmetic: no red box → the downward arrow never fires.

PICTURE. Empty outer table. Left (uncorrelated): the inner box glows red once regardless — labelled " $c$ ". Right (correlated): the inner box stays black, never fires — labelled " $0$ ".

Step 7 — Edge case: the optimizer erases the difference

WHAT. The naive picture (Step 4) says correlated costs $N \cdot c$ . But the parent's steel-man warns this is only the logical semantics. Watch what the optimizer does.

WHY. The optimizer notices that "for each dept, compute its average once" is wasteful if many rows share a dept. It rewrites the correlated subquery — grouping by dept_id a single time (a GROUP BY) and joining the result back (a join / semi-join). The $N$ repeated evaluations collapse toward one grouped pass. So the effective cost slides from $N \cdot c$ back down toward $c$ .

PICTURE. Top row: $N$ separate red inner-box firings (naive). An arrow labelled "optimizer rewrite" points down to: a single grouped table computed once in red, then joined. The tall stack of red firings collapses into one.

The one-picture summary

One figure, whole derivation. The single question "does a value flow into the inner box?" branches the world: no arrow → sealed → runs once → cost $c + N k$ → copy-paste-able → prefer IN; arrow in → runs per row → naive cost $N c + N k$ → errors if run alone → prefer EXISTS → but the optimizer can collapse it back toward $c$ .

Recall Feynman retelling — the whole walkthrough in plain words

Picture a grid of employees, and a red spotlight shining on one row at a time as it slides down. Inside your query you draw a little box that computes some number.

If that box only looks at the whole table (like the overall average salary), the spotlight's position doesn't matter to it — so you compute it once, write it on the board, and every row compares against that same board number. That's uncorrelated: sealed box, one evaluation, cheap.

If that box peeks at the red spotlighted row (like "this person's department"), then moving the spotlight changes the box's answer — so the box must be recomputed each time the light moves. That's correlated: a value flows into the box, once per row.

To tell them apart you don't need performance theory — just look inside the box for a name that belongs to the outer row. See one? An arrow flows in → correlated (and it would crash if you ran it alone, because that name has no value). See none? → uncorrelated (copy-paste it and it runs fine).

Two twists at the edges: with zero outer rows the sealed box still fires once (it runs up front), while the peeking box never fires (no spotlight, no value). And even when the peeking box looks like it runs $N$ times, a smart optimizer often reorganises the work — group once, join back — so it costs about the same as running once. The logical picture is "loop"; the real machine is often "one clever pass."

Connections

Subqueries — correlated vs uncorrelated (index 4.4.7) — the parent this page derives.
EXISTS vs IN vs ANY — the sealed-box vs arrow-in split decides which you reach for.
Query Optimizer Execution Plans — Step 7: how $N \cdot c$ collapses toward $c$ .
Joins — inner outer cross — the shape the optimizer rewrites correlation into.
Aggregate Functions GROUP BY — the "compute per group once" pass in the rewrite.
NULL handling in SQL — why the IN branch of the summary needs care.