Correlated Subquery

Hook: A correlated subquery is SQL’s "ask this question for every row" pattern, which interviewers love because it reveals whether you understand row-by-row context instead of just memorizing syntax.

Question: What is a correlated subquery in SQL?

Answer: A correlated subquery is a subquery that references a column from the outer query, so it depends on the current outer row. That means the database must evaluate the inner query in the context of each row from the outer query. It is often used for row-specific checks like "does this customer have any orders?" or "what is this customer’s latest order date?"

Interview-Ready Answer: A correlated subquery is a subquery that uses a value from the outer query, so its result changes for each outer row. I use it when I need a per-row lookup or existence check, especially with EXISTS or a scalar lookup. One important detail is that the optimizer may rewrite it, but logically it behaves like a row-by-row evaluation.

What it is

A correlated subquery is a subquery that cannot stand alone, because it points back to the outer query. The inner query contains an outer reference (a column from the outer query), so the inner result depends on the current outer row.

How it works under the hood

1. The database reads one row from the outer query.
2. It substitutes that row’s value into the correlated subquery.
3. It runs the inner query with that value as a filter or comparison.
4. If the subquery uses EXISTS, it can stop as soon as it finds one match.
5. If the subquery is a scalar subquery, it must return exactly one value; zero rows usually becomes NULL, and more than one row is an error in most databases.
6. The engine may keep doing this row-by-row, or it may rewrite the query into a join or semi-join if it can prove the result is the same.

When and why to use it

• Existence checks: "Show customers who have at least one paid order."
• Top-per-group lookups: "Show each customer’s latest order date."
• Row-specific conditions: "For each employee, compare salary to their department average."

Think of it as a flashlight that follows each outer row and asks the inner table, "What do you know about this one?"

Correlated subquery vs alternatives

A JOIN is usually the first rewrite to consider if you just need to combine rows. A window function is often cleaner for ranking or latest-row problems. Correlated subqueries are still excellent when the logic is naturally "for each row, ask a question."

Performance notes

In the simplest mental model, a correlated subquery can behave like nested loops: if the outer query has N rows and the inner lookup scans M rows, the cost can drift toward O(N × M). With a useful index on the correlated column, the inner lookup may become much faster, closer to O(N log M) in practice. Many modern optimizers can decorrelate some queries into semi-joins or hash joins, but you should not assume that always happens; check the plan with EXPLAIN.

Real numbers matter: 10,000 outer rows against a 1,000,000-row inner table is fine if the inner predicate is indexed, but it can become painfully slow if every outer row forces a table scan. This is why interviewers care about correlated subqueries: the syntax is small, but the performance impact can be huge.

Important edge cases

• Scalar subquery returns many rows: that is usually an error. Use MAX, MIN, COUNT, or a more precise filter.
• No matching rows: a scalar subquery often returns NULL, which can change comparisons in subtle ways.
• NOT IN with NULL: many candidates confuse this with correlated logic; NOT EXISTS is often safer for anti-joins.
• Optimizer rewriting: even if the SQL looks row-by-row, the engine may execute it differently.

Real-world story

Imagine a checkout service for a subscription app. The product team wants a dashboard that shows each customer and the date of their most recent payment attempt. A developer writes a correlated subquery to fetch the latest order date for each customer, because the question is naturally per-customer.

The bug happens when someone removes the aggregate and accidentally writes a scalar subquery that can return multiple payment rows. In PostgreSQL this becomes an error like "more than one row returned by a subquery used as an expression"; in SQL Server it looks like "Subquery returned more than 1 value." The dashboard job fails, alerts fire, and operators see an empty report even though the data is fine. The fix is to make the subquery return one row per outer row, usually with MAX, MIN, or a window function.

Symptoms: failed nightly job, missing dashboard numbers, error logs from the database layer. User impact: managers think payments are broken, even though the real issue is the query shape.

Follow-up & Tricky Questions

• How is a correlated subquery different from a JOIN? A JOIN combines rows from two tables at once, while a correlated subquery asks a question per outer row. If you only need matching data, a JOIN is often simpler; if you need a row-specific yes/no check, correlated EXISTS can be very clear.
• When should I use EXISTS instead of IN? Use EXISTS when you care about whether at least one matching row exists, especially if the inner query may contain NULL. It also short-circuits naturally, which can be efficient.
• Can the optimizer remove the correlation? Yes, sometimes. The database can rewrite the query into a semi-join or another plan, but you should verify that with EXPLAIN rather than assuming it.
• What happens if a scalar subquery returns more than one row? Most databases raise an error, because a scalar expression must produce one value only. That is why aggregates or tighter filters are often needed.
• Can correlated subqueries appear in SELECT, WHERE, and HAVING? Yes. The placement changes the use case: in SELECT they often compute a per-row value, and in WHERE or HAVING they often filter rows.
• Does every correlated subquery literally run once per row? Logically yes, but physically no guarantee. The optimizer may batch, rewrite, or choose a different access path, so the final execution plan is what matters.
• Is NOT IN the same as NOT EXISTS? No. NOT IN can behave unexpectedly when the inner query contains NULL, while NOT EXISTS is usually the safer anti-match pattern.
• Can I replace a correlated subquery with a window function? Often yes, especially for top-per-group problems like latest order per customer. Window functions can be more readable and frequently more efficient.

Common Mistakes:

• Using a scalar subquery that returns many rows; fix: add an aggregate like MAX or redesign to EXISTS/JOIN.
• Assuming correlated subqueries are always slow; fix: check the index and the execution plan, because optimizers often rewrite them.
• Using NOT IN where NOT EXISTS is safer; fix: remember that NULL can break NOT IN logic.
• Forgetting the outer reference in the inner query; fix: make sure the inner query truly depends on the current outer row.

Memory Hook: "A correlated subquery is a flashlight that follows each row." The outer row is the person holding the flashlight, and the inner query answers only for that one person.

Cheat Sheet:

• Correlated = inner query references outer query.
• Great for EXISTS, NOT EXISTS, and per-row lookups.
• Scalar subquery must return one value.
• Performance can look like nested loops unless optimized.
• Use EXPLAIN to confirm the real plan.

Practice Tasks:

• Write a query that lists employees whose salary is above their department average using a correlated subquery.
• Rewrite that query using a JOIN or a window function and compare readability.
• Write a NOT EXISTS query that finds customers with no paid orders.