Correlated Subqueries

A correlated subquery references a column from the outer query in its WHERE clause (or other clauses). This creates a dependency on the outer query's current row — the subquery cannot be evaluated independently because it needs the current outer row's values to execute. A non-correlated subquery is entirely self-contained; it executes once and produces a fixed result that the outer query reuses for every row.

The execution difference is fundamental. A non-correlated subquery: execute inner query once → cache result → outer query uses the cached result for every row it evaluates. One inner execution total. A correlated subquery: for each outer row → substitute outer row's column values into the inner query → execute inner query → use the result for this specific outer row → move to next outer row → repeat. N inner executions for N outer rows.

This execution model makes correlated subqueries O(n) in the number of outer rows — one inner execution per outer row. If the inner query is itself expensive (full table scan, complex join), total cost is O(n × inner_cost), which becomes O(n²) when the inner query scans the same table as the outer. For small tables this is unimportant. For large production tables with millions of rows, this is the difference between a 100ms query and a 10-minute query. The solution for large tables is to rewrite the correlated pattern as a JOIN to a pre-aggregated derived table or a window function — both compute the aggregate once rather than once per outer row.

The rewrite follows a consistent pattern. Identify what the correlated subquery computes — typically an aggregate (AVG, MAX, MIN, COUNT) per group defined by the correlation column. Move that computation into a standalone derived table or CTE that groups by the correlation column. Then JOIN the outer query to that derived table on the correlation column.

Example: SELECT * FROM products AS p WHERE p.unit_price > (SELECT AVG(p2.unit_price) FROM products p2 WHERE p2.category = p.category). The correlated subquery computes the average price per category, once per product row. Rewrite: compute category averages once in a derived table: (SELECT category, AVG(unit_price) AS cat_avg FROM products GROUP BY category) AS cat_avgs. Then join: SELECT p.* FROM products AS p JOIN cat_avgs ON p.category = cat_avgs.category WHERE p.unit_price > cat_avgs.cat_avg.

The performance improvement: the derived table computes one AVG per category (say 8 categories → 8 aggregations). The correlated version computes one AVG per product row (say 50 products → 50 aggregations, each scanning 50 rows → 2,500 total row operations). The JOIN version: 50 rows for the aggregation + 50 rows for the outer query = 100 total row operations. For 1 million products across 100 categories: correlated = 10^12 operations, JOIN = ~2,000,000 operations. The JOIN is orders of magnitude faster. This same rewrite pattern applies to any correlated aggregate: compute the aggregate per group in a derived table, join the outer query to it.

These three approaches solve the same problem — finding rows in one table that do or do not have a matching row in another — but with different semantics, NULL behaviour, and performance characteristics.

EXISTS (and NOT EXISTS) is the preferred general-purpose approach. It is semantically clear ("does at least one related row exist?"), always NULL-safe, and short-circuits on the first match — stopping the inner scan as soon as one matching row is found. This makes it very efficient when matches are common. NOT EXISTS is the safest anti-join pattern — it correctly handles NULL values in the correlated column, unlike NOT IN. Use EXISTS/NOT EXISTS as the default for existence checks.

IN (and NOT IN) is readable for small fixed lists (IN (1, 2, 3)) and reasonable for small subquery results. NOT IN is dangerous: if the subquery returns any NULL value, NOT IN returns zero rows for every outer row — a silent correctness bug. Only use NOT IN when you are certain the subquery column cannot contain NULLs, ideally enforced by a NOT NULL constraint.

LEFT JOIN + WHERE IS NULL is the anti-join alternative — functionally equivalent to NOT EXISTS for non-NULL cases. It is familiar to analysts who think in terms of JOINs and allows selecting right-table columns in the result (EXISTS cannot). On large tables, the query optimiser typically converts both NOT EXISTS and LEFT JOIN IS NULL to the same physical anti-join plan, making their performance equivalent. Choose based on readability: EXISTS if you want to express existence semantics explicitly; LEFT JOIN IS NULL if you are already in a JOIN-heavy query and consistency matters.

Both correlated subqueries and window functions compute an aggregate value per group for each row — for example, the department average salary shown alongside each employee's salary. But they work very differently at the execution level and have different performance characteristics.

A correlated subquery executes separately for each outer row. For 1,000 employees across 20 departments, it runs 1,000 subquery executions — one per employee — even though there are only 20 distinct department averages to compute. A window function (AVG(salary) OVER (PARTITION BY department)) makes a single pass through the data, computes all department averages simultaneously, and attaches the correct average to each row. One pass for all 1,000 employees regardless of group count.

Window functions are almost always faster for per-group aggregate computations. They are also more composable — you can compute multiple window functions in the same SELECT without multiple subquery executions: SELECT salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg, MAX(salary) OVER (PARTITION BY dept) AS dept_max, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees. Three group-level computations in one pass. The equivalent with correlated subqueries requires three separate subquery executions per row. The limitation of window functions: they cannot be used in WHERE directly (only in a wrapping subquery or CTE) and they require database support (all major databases support them today). Correlated subqueries in WHERE remain more natural for filtering based on group conditions when window functions are not available or when the logic is clearest expressed as a row-level existence check.

The most recent order per customer requires finding, for each customer, the order whose date equals the maximum order date among all orders for that customer. The correlated subquery pattern: SELECT * FROM orders AS o WHERE o.order_date = (SELECT MAX(o2.order_date) FROM orders AS o2 WHERE o2.customer_id = o.customer_id). For each order row in the outer query, the subquery computes the maximum order date for that order's customer — if the current order's date equals that maximum, it is the most recent.

This works but executes once per order row. For a customer with 10 orders, the subquery runs 10 times — each time returning the same MAX date — and only the one matching order survives. Better approaches for scale: JOIN to a derived table of max dates: FROM orders AS o JOIN (SELECT customer_id, MAX(order_date) AS max_date FROM orders GROUP BY customer_id) AS latest ON o.customer_id = latest.customer_id AND o.order_date = latest.max_date. Or DISTINCT ON (PostgreSQL): SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC.

The cleanest modern approach uses window functions: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) AS ranked WHERE rn = 1. ROW_NUMBER() assigns rank 1 to the most recent order per customer — a single pass through the data. This is the "top N per group" pattern that window functions handle cleanly and efficiently. For the specific case of "most recent one record per group," DISTINCT ON in PostgreSQL is often the most concise: SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, total_amount FROM orders ORDER BY customer_id, order_date DESC — this returns exactly one row per customer, the one with the latest order_date.

Cause: The correlated subquery returns NULL when no rows match the correlation condition. This happens when an outer row's correlated column value does not appear in the inner table — for example, a customer with no orders produces NULL from (SELECT AVG(total_amount) FROM orders WHERE customer_id = c.customer_id). Comparing the outer column to NULL (col > NULL) evaluates to NULL, which WHERE discards — the outer row disappears from the result.

Fix: Handle the NULL with COALESCE: WHERE col > COALESCE((SELECT AVG(...) ...), 0). Or use IS NOT NULL to exclude outer rows where the subquery returns NULL: AND (SELECT AVG(...) ...) IS NOT NULL. Or change the logic to EXISTS if the intent is to check whether the related record exists at all. Decide which is semantically correct: should rows with no correlated data be included (COALESCE) or excluded (natural NULL filtering)?

Cause: The correlated subquery in SELECT or WHERE with = is returning more than one row. A correlated subquery in SELECT or with = must return exactly one row — it is expected to be a scalar. If the correlation is on a non-unique column, multiple rows may match and the subquery returns multiple values, violating the scalar requirement.

Fix: Add an aggregate to force exactly one row: = (SELECT MAX(...) ... WHERE ...) or = (SELECT MIN(...) ...). If multiple matching rows are intentionally valid, use IN instead of =: WHERE col IN (SELECT ... WHERE correlation). Or add LIMIT 1 with ORDER BY if you want the top matching row: = (SELECT val FROM ... WHERE ... ORDER BY col DESC LIMIT 1). If the multiple rows represent a one-to-many relationship, a JOIN may be more appropriate than a scalar subquery.

Cause: The correlated subquery executes once per outer row — 100,000 executions for 100,000 outer rows. Each inner execution may scan a large portion of the inner table. Total work: 100,000 × inner_scan_cost. If the inner table has no index on the correlated column (the join key), each inner execution is a full table scan, making total cost O(n²).

Fix: First: add an index on the correlated column in the inner table. CREATE INDEX ON orders(customer_id) — this makes each inner execution an index lookup rather than a full scan. Second: rewrite as a JOIN to a pre-aggregated derived table — compute the aggregate once per group, then JOIN. Third: rewrite as a window function for per-group aggregates — single pass through the data. Use EXPLAIN ANALYZE to see the current execution plan and verify the rewrite improvement.

Cause: The EXISTS subquery is missing its correlated condition — the WHERE clause inside EXISTS does not reference the outer query's table. EXISTS (SELECT 1 FROM orders) without a WHERE that links to the outer row simply checks whether the orders table has any rows at all — which is always TRUE if the table is non-empty. The result is that EXISTS is always TRUE for every outer row, making the WHERE EXISTS filter a no-op.

Fix: Add the correlated condition: WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id). The inner WHERE must reference the outer alias (c.customer_id) to make the subquery dependent on the current outer row. To verify, temporarily change EXISTS to a SELECT * and add the correlated condition — the result should vary by outer row, returning different rows for different customers.

Cause: The NOT EXISTS correlated condition is too broad or inverted. A common mistake: NOT EXISTS (SELECT 1 FROM orders WHERE customer_id IS NOT NULL) — this checks whether any order exists with any non-NULL customer_id, which is always TRUE (there are always orders), so NOT EXISTS is always FALSE and no rows are returned — or worse, the condition is never satisfied. Another mistake: using the wrong column in the correlation.

Fix: Verify the correlated condition links specifically to the outer row: NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id). The correlation must use the outer row's specific value — not a generic condition. Test by temporarily changing NOT EXISTS to EXISTS and verifying it returns the correct rows (the ones you want to exclude). Then flip back to NOT EXISTS. Also confirm column names and aliases are correctly prefixed.

Write three separate queries using correlated subqueries: (1) Find all orders where the total_amount is above the average total_amount for orders from the same store. Show order_id, store_id, total_amount, and the store's average as store_avg_amount. Only include delivered orders. (2) Find all customers who have placed at least one order in every month that has order data (i.e., they ordered in January AND February 2024). Use a correlated EXISTS or NOT EXISTS approach. Show customer_id and full name. (3) Find each product along with the count of other products in the same category that are cheaper than it (cheaper_in_category count). Show product_name, category, unit_price, and cheaper_in_category. Sort by category then cheaper_in_category descending.