SQL Roadmap/Subqueries & CTEs

Correlated Subqueries & EXISTS

A non-correlated subquery runs once and hands its result to the outer query; a correlated subquery re-runs once per outer row because it references that row's own columns — the mechanism behind "compare each row to its group's aggregate."

!!3/5Theory: 25m3 problems

Non-correlated subqueries: run once, hand off the result

A non-correlated (or "nested") subquery is completely independent of the outer query — it could run on its own, in a separate query window, and its result would be identical either way:

SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

The inner SELECT AVG(salary) FROM employees executes exactly once, producing a single scalar value, which the outer query then compares every row against. This is cheap and easy to reason about: think of the subquery as being evaluated first and substituted in as a literal.

Correlated subqueries: re-run once per outer row

A correlated subquery references a column from the outer query inside its own WHERE clause, which means it cannot be evaluated independently — it must be re-evaluated once for every row the outer query considers:

SELECT e1.name, e1.salary, e1.department_id FROM employees e1 WHERE e1.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id -- references the OUTER row );

Here, e1.department_id inside the inner query is what makes it correlated — for every outer row, the subquery recomputes the average salary for that row's specific department, rather than the whole company's average. This is the standard pattern for "compare each row to its own group's aggregate," and it directly generalizes the plain GROUP BY/HAVING pattern from the previous topic to per-row (not per-group) results.

The cost is real: naively, a correlated subquery re-runs once per outer row, which is O(n·m) in the worst case rather than the single aggregation pass a GROUP BY needs. Modern query planners frequently rewrite correlated subqueries into an equivalent join internally, so the naive mental cost model isn't always the real-world cost — but it's still worth knowing the pattern's naive complexity, and worth knowing that a window function (AVG(salary) OVER (PARTITION BY department_id), covered in the Window Functions topic) is frequently the cleaner, and often faster, modern alternative to exactly this correlated-subquery pattern.

EXISTS and NOT EXISTS

EXISTS is a boolean test: it's TRUE if the correlated subquery returns at least one row, and it stops scanning as soon as it finds one (a real optimization — it never needs to count or materialize the full match set, just detect the first hit). This makes it the standard, NULL-safe way to write "at least one matching row exists" or (as covered as an anti-join in the previous topic) "no matching row exists":

SELECT c.customer_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 1000 );

The SELECT 1 inside an EXISTS subquery is a convention, not a requirement — EXISTS only cares whether any row comes back, never what columns or values it contains, so SELECT 1, SELECT *, or SELECT o.id are all functionally identical; SELECT 1 just signals "the columns genuinely don't matter here" to any future reader.

The "greatest-N-per-group" pattern

A frequent interview shape — "the highest-paid employee(s) in each department" — is a direct application of a correlated subquery:

SELECT e.name, e.department_id, e.salary FROM employees e WHERE e.salary = ( SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id );

This correctly returns ties (two employees in the same department both at the department max both appear), which is often exactly the desired behavior and is worth calling out explicitly, since it's a meaningfully different result than "the one row with rank 1" that a window-function ROW_NUMBER() approach (covered in Ranking Functions) would give you without an extra tie-breaking step.

Why interviewers care

Correlated subqueries are where "I can write a SELECT" becomes "I can reason about per-row-dependent computation," which is a genuinely different skill. They're also a natural on-ramp to a very common interview follow-up: "can you write this without a subquery, using a window function instead?" — being able to translate fluently between the two (correlated subquery vs. window function) for the same greatest-N-per-group problem is a strong senior signal, and is exactly why this roadmap revisits the same class of problem again in the Window Functions topic.

Pitfalls and interview gotchas

  • Using IN with a subquery that can return NULL, the same three-valued-logic trap from Set Operations & Anti-Joins — IN itself is safe (unlike NOT IN), but it's worth remembering the asymmetry: IN with a NULL in the list is fine, NOT IN with a NULL in the list is broken.
  • Forgetting a correlated subquery re-evaluates per outer row and reaching for one inside a very large outer query without considering whether a window function or join rewrite would be more efficient.
  • Using = instead of IN/EXISTS/an aggregate when a subquery can return more than one row — a scalar subquery that unexpectedly returns multiple rows is a runtime error on most engines, not a silent bug, but it's a common one to trigger by accident (e.g., forgetting a GROUP BY inside the subquery).
  • Not recognizing the greatest-N-per-group pattern and reaching for a much more convoluted approach (e.g., self-joins with inequality conditions) when a simple correlated MAX/MIN comparison would do.

Where this goes next

CTEs & Recursive Queries (next) introduces the WITH clause for naming and reusing subquery logic, then extends it to WITH RECURSIVE — the one tool on this roadmap that can traverse a hierarchy of genuinely unknown depth, which no amount of subquery nesting or self-joining can do cleanly.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.