SQL / SUBQUERIES AND CTES
Correlated subqueries and how they run row by row
Write subqueries that reference the outer row, reason about their per-row evaluation, and avoid the scoping and NULL traps correlation brings.
What you will learn
- Spot correlation by finding a reference to an outer table's column inside the subquery
- Alias the outer table so the inner query can filter against the current row's values
- Test a correlated subquery by substituting a literal for its outer reference
- Guard a correlated UPDATE with WHERE EXISTS so unmatched rows are not set to NULL
Understanding Correlated subqueries and how they run row by row
An ordinary subquery is a closed expression: nothing inside it depends on the query around it, so the engine can work out one answer and reuse it. A correlated subquery breaks that independence by naming a column that belongs to the outer query, as in WHERE x.dept = e.dept where e is the outer table. From that moment the subquery has no value of its own, only a value per outer row, which is exactly why pasting it into an editor by itself fails.
The mental model is a loop. For each candidate row of the outer query the engine binds the outer columns to that row's values, runs the inner query as though those values were literals, and feeds the result into the surrounding comparison. Scope flows one way only: the inner query can see the outer aliases, but the outer query can never see the inner table's columns. Unqualified names resolve from the innermost scope outward, so a bare dept inside the subquery means the inner table's dept, not the outer one's.
Row by row describes the answer, not the plan. SQLite, PostgreSQL and MySQL all rewrite many correlated subqueries into joins, semi-joins or a single grouped aggregate, so the loop you pictured may never execute; EXPLAIN is the only way to know. What does follow from correlation is that cardinality becomes data dependent: when the inner query matches nothing for some outer row the scalar result is NULL, and when it matches two rows a comparison with = fails at runtime on that one row.
CREATE TABLE employee (
id INTEGER PRIMARY KEY,
name TEXT,
dept TEXT,
salary INTEGER
);
INSERT INTO employee (id, name, dept, salary) VALUES
(1, 'Ada', 'eng', 9000),
(2, 'Brij', 'eng', 7000),
(3, 'Cato', 'eng', 9000),
(4, 'Dara', 'sales', 5000),
(5, 'Emil', 'sales', 6500);
-- e.dept inside the subquery is what makes this correlated:
-- MAX is recomputed for the department of the row being tested.
SELECT e.name, e.dept, e.salary
FROM employee AS e
WHERE e.salary = (SELECT MAX(x.salary)
FROM employee AS x
WHERE x.dept = e.dept)
ORDER BY e.dept, e.name;A subquery that references a column of the outer query has no single value; it is evaluated once per outer row with that row's values bound in as constants.
Worked examples
Correlation in the SELECT list
Each output row carries a count computed against its own department, using that row's salary as the threshold.
SELECT e.name,
e.dept,
e.salary,
(SELECT COUNT(*)
FROM employee AS c
WHERE c.dept = e.dept
AND c.salary > e.salary) AS higher_paid
FROM employee AS e
ORDER BY e.dept, e.salary DESC, e.name;Example explained
Line 1c.salary > e.salary compares an inner row against the outer row, so the count is relative to the row being printed rather than to the table.
Line 2Ada and Cato tie at 9000 and neither counts the other, because the predicate is strictly greater than.
Line 3Brij gets 2 rather than 3 because c.dept = e.dept discards the sales rows before the salary test runs.
Line 4The subquery sits in the SELECT list, so it is evaluated for every row the query emits, not once for the statement.
Correlated UPDATE and the NULL it can write
A correlated subquery fills a column from another table, and silently blanks the rows that have no match.
CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, last_price INTEGER);
CREATE TABLE sale (id INTEGER PRIMARY KEY, product_id INTEGER, price INTEGER, sold_on TEXT);
INSERT INTO product (id, name, last_price) VALUES
(1, 'bolt', NULL), (2, 'nut', NULL), (3, 'washer', NULL);
INSERT INTO sale (id, product_id, price, sold_on) VALUES
(1, 1, 120, '2026-01-10'),
(2, 1, 130, '2026-02-02'),
(3, 2, 45, '2026-01-20');
UPDATE product
SET last_price = (SELECT s.price
FROM sale AS s
WHERE s.product_id = product.id
ORDER BY s.sold_on DESC
LIMIT 1);
SELECT id, name, last_price FROM product ORDER BY id;Example explained
Line 1s.product_id = product.id correlates the subquery to the row being updated, so each product looks up only its own sales.
Line 2ORDER BY sold_on DESC with LIMIT 1 keeps the subquery scalar; without it bolt's two sales would abort the statement.
Line 3washer has no sales, the subquery returns zero rows, and a scalar subquery over zero rows is NULL, so the update overwrites the column instead of skipping the row (some clients print that NULL as an empty cell).
Line 4Adding WHERE EXISTS (SELECT 1 FROM sale AS s WHERE s.product_id = product.id) to the UPDATE limits it to matched rows.
Important notes
Per-row evaluation defines the result, not the execution: the same correlated query can run as a nested loop on one engine and as a single grouped aggregate on another.
Because the inner result depends on the outer row, a subquery compared with = can pass on sample data and later raise 'more than one row returned' as soon as one outer row matches two inner rows.
Common mistakes
Writing WHERE dept = dept inside the subquery: both names resolve to the inner table, the predicate is always true, and every outer row silently gets the global maximum instead of its group's maximum.
Copying the inner query into a separate tab to test it and concluding the database is broken when it errors with 'no such column: e.dept'; a correlated subquery has no meaning outside its outer query, so test it with a literal in place of the outer reference.
Running a correlated UPDATE with no WHERE EXISTS guard, which replaces existing values with NULL on every row the subquery finds no match for.
Try it yourself
Change, predict, then run
Insert a fourth eng employee earning 9500 and re-run the main query to see which rows change, then rewrite the WHERE clause so it returns everyone paid strictly less than the average salary of their own department.
Open the SQL workspaceCheck your understanding
In the query SELECT e.name FROM employee AS e WHERE 2 > (SELECT COUNT(*) FROM employee AS c WHERE c.dept = dept); the column dept inside the subquery has no table prefix. Against the five-row employee table, what does the query actually do?
- It counts each employee's departmental colleagues, exactly as if dept had been written e.dept.
- The bare dept resolves to the inner table, so the predicate is always true, COUNT(*) is 5 for every outer row, and no rows are returned.
- The engine rejects the statement with an ambiguous column error, since dept exists in both employee references.
- The subquery returns NULL for every outer row, so the comparison is unknown and the correlation is ignored.
Show answer
Names resolve from the innermost scope outward, and the inner FROM already supplies a dept column, so dept means c.dept; c.dept = c.dept holds for every row, COUNT(*) is 5 for all five outer rows, and 2 > 5 filters everything out. Option 3 is tempting because dept looks ambiguous, but ambiguity is judged inside a single scope and the inner scope has exactly one dept column, so the correlation vanishes silently rather than raising an error.