Nested Subquery

Think of a nested subquery like a note inside a note: the inner query answers a smaller question first, and the outer query uses that answer to make the final decision.

Question: What is a nested subquery in SQL?

Answer: A nested subquery is a SQL query written inside another SQL query. The inner query can produce a single value, a list of values, or a temporary row set, and the outer query uses that result to filter, compare, or calculate something else. It is useful when one question depends on the answer to another question.

Interview-Ready Answer: “A nested subquery is a query inside another query. I use it when the outer query needs a value or set of rows produced by an inner query, like comparing to an average or filtering by a computed list. One detail I always mention is that correlated subqueries can be much slower because the inner query may run once per outer row, while an uncorrelated subquery usually runs once.”

What it is

A nested subquery is just a query inside another query. The inner query can be a scalar subquery, meaning it returns one value; a row or table subquery, meaning it returns multiple rows or columns; or a correlated subquery, meaning the inner query refers to columns from the outer query. If it does not depend on the outer query, it is uncorrelated.

How it works under the hood

1. The SQL parser builds a query tree and places the inner query inside the outer one.
2. The optimizer checks whether the subquery can be flattened, which means rewritten into a join-like form for a cheaper plan.
3. If the subquery is uncorrelated, the database often computes it once and reuses the result.
4. If the subquery is correlated, the database may evaluate it for each outer row, although the optimizer can sometimes rewrite it into a semi-join or anti-join. A semi-join returns rows only when a match exists; an anti-join returns rows only when no match exists.
5. The outer query then uses the inner result in WHERE, SELECT, HAVING, or FROM.

When and why to use it

• Use it when you need a result first, then want to compare outer rows against that result.
• Use it for clean one-off logic, especially when the inner result is small and easy to reason about.
• Use it for existence checks with EXISTS or IN, and for anti-filters with NOT EXISTS.

Compared with joins and CTEs

In newer PostgreSQL versions, many non-recursive CTEs can be inlined instead of forced into a separate step; older versions treated them more like an optimization fence, which could block rewrites. That is why a CTE can be easier to read without always being slower, but the exact behavior depends on the database and version.

Performance and edge cases

• An uncorrelated subquery is often close to O(n + m) overall because the inner query runs once and the outer query scans once.
• A correlated subquery can degrade toward O(n × m) in the worst case if the inner query is repeated for each outer row and no useful index exists.
• Realistic example: 50,000 customers and 1,000,000 orders can become painfully slow if the database must re-scan orders for each customer. With a good index on orders(customer_id), the same logic can shrink to many cheap index lookups instead of huge full scans.
• Be careful with NOT IN and NULL. If the subquery returns even one NULL, the comparison becomes unknown and can eliminate every row.
• A scalar subquery must return exactly one row. If it returns two rows, most databases raise an error instead of guessing.

Memory wise, a good way to think about nested subqueries is: the inner query is the calculator, the outer query is the decision-maker. If the calculator is reused once, great. If it is run again for every row, you should suspect a performance problem.

Real-world story

Imagine a checkout service for an online store. The product team asks for a report of customers whose lifetime spend is above the average customer spend, so the team writes a nested subquery to compute each customer total and compare it with the overall average.

It works in staging, but in production the query is correlated and runs against millions of order rows. The database starts doing repeated scans, CPU jumps to 100%, and the checkout API p95 latency climbs from 120 ms to 8 seconds. In logs, you see statement timeout errors and a slow-query alert tied to the report endpoint. Customers notice spinning loaders, delayed checkout confirmations, and retry storms that make the database even hotter.

The fix is usually to add the right index, rewrite the nested subquery into a clearer join or pre-aggregated CTE, and test on production-sized data. The lesson: nested subqueries are fine, but you need to know whether the inner query is run once or many times.

Follow-up & Tricky Questions:

• Follow-up: What is the difference between a correlated and an uncorrelated subquery? An uncorrelated subquery does not depend on the outer row, so it usually runs once. A correlated subquery references outer columns, so it may run once per outer row unless the optimizer rewrites it.
• Follow-up: When would you choose a subquery instead of a JOIN? I choose a subquery when I want a compact filter or a one-off comparison, especially for EXISTS or IN. I choose a JOIN when I need to return columns from both sides or make the relationship between rows explicit.
• Follow-up: Can the optimizer rewrite a nested subquery? Yes. Many databases can flatten a subquery into a join, semi-join, or anti-join if that produces the same result and a better plan.
• Follow-up: Where can nested subqueries appear? They can appear in WHERE, SELECT, HAVING, and FROM. The exact placement changes whether you are filtering rows, computing a value, or building a derived table.
• Follow-up: How do you make a nested subquery faster? Add the right index, remove repeated scans by rewriting a correlated query, and test with realistic row counts. If the logic is step-by-step, a CTE or JOIN is often easier for the optimizer and for humans.
• Tricky: Why can NOT IN return no rows when the subquery contains NULL? SQL treats that comparison as unknown, not true or false, so every candidate row can fail the test. NOT EXISTS is usually the safer choice.
• Tricky: Does a scalar subquery always work if it returns multiple matches? No. A scalar subquery must return exactly one value; if it returns more than one row, most databases raise an error.
• Tricky: Does the inner query always execute first in the physical plan? Logically, yes, the outer query depends on the inner result. Physically, the optimizer may reorder or flatten the work so the plan looks very different from the written SQL.

Common Mistakes:

• Forgetting the NULL trap with NOT IN. Correction: if NULLs are possible, prefer NOT EXISTS or filter NULLs explicitly.
• Using a correlated subquery on a big table without an index. Correction: check whether the inner query repeats for every outer row; if yes, add an index or rewrite it.
• Expecting a scalar subquery to return many rows. Correction: a scalar subquery must return one value only; otherwise it errors.
• Writing the most compact version instead of the clearest one. Correction: in interviews and real code, readability matters; a JOIN or CTE is often easier to maintain.

Memory Hook: Think: inner answers, outer decides. The inner query is the helper; the outer query is the boss.

Cheat Sheet:

• Nested subquery = query inside query.
• Uncorrelated usually runs once.
• Correlated may run per outer row.
• EXISTS is great for existence checks.
• NOT EXISTS is safer than NOT IN with NULLs.
• CTEs can make multi-step logic easier to read.

Practice Tasks:

1. Write a subquery that finds employees whose salary is above the company average.
2. Rewrite that query once with a JOIN and once with a CTE, then compare readability.
3. Add a NULL to the inner table and observe how NOT IN behaves versus NOT EXISTS.