Subqueries & CTEs
SQL · 6 interview questions
A subquery is a query used as an expression. It can be scalar (one value, usable anywhere a value fits), a list (usable with IN), a table (usable in FROM), or correlated — referencing a column from the outer query, which means it conceptually re-runs per outer row.
Correlated subqueries are the ones to be careful with. They're expressive and they read naturally, but the mental model is a loop, and on a large table that loop is the performance problem. Most correlated subqueries can be rewritten as a join or a window function, and being able to say that out loud is worth more in an interview than the query itself.
EXISTS is the correlated form worth loving. It stops at the first matching row rather than materialising the whole set, and unlike IN it's immune to the NULL trap that makes NOT IN return an empty result. When the question is "does a matching row exist", EXISTS says exactly that.
A CTE — the WITH name AS (...) form — is a subquery you've given a name and pulled to the top. It doesn't usually change what the database does, but a three-level nested subquery and a sequence of three named CTEs are the difference between a query someone can review and one they can't.
Subqueries & CTEs interview questions
- What makes a subquery correlated?
- It references a column from the outer query, so it can't be evaluated independently — conceptually it re-runs for each outer row. An uncorrelated subquery is evaluated once and its result reused.
- Why they ask: The definition matters because it's also the performance warning.
- When would you use EXISTS instead of IN?
- When the subquery might return NULLs —
NOT INreturns nothing at all if any NULL is present, whileNOT EXISTSbehaves as intended. EXISTS also short-circuits on the first match rather than building the full list, which matters on large subqueries. - Why they ask: The NULL half of this answer is the one that impresses.
- How do you find the second highest salary, and what must the query return if there isn't one?
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp). It must return NULL, not an empty result — and wrapping the query inSELECT (…) AS SecondHighestSalaryguarantees a row even when nothing qualifies. A LIMIT/OFFSET version returns no rows instead, which fails the requirement unless you wrap it too.- Why they ask: The canonical SQL interview question, and the NULL requirement is the part people fail.
- What does a CTE give you that a subquery doesn't?
- Readability and reuse: it's named, it sits at the top instead of buried mid-query, and it can be referenced more than once. It also enables recursion via
WITH RECURSIVE. Performance is usually the same — most engines inline a non-recursive CTE. - Why they ask: 'It's faster' is the wrong answer and a common one.
- Why does a subquery in the FROM clause need an alias?
- Because it produces a derived table, and every table in a FROM clause needs a name to be referenced by. MySQL and Postgres both error without one —
Every derived table must have its own alias. - Why they ask: A five-second fix that costs people minutes in a live coding round.
- What happens if a scalar subquery returns more than one row?
- It's a runtime error —
Subquery returns more than 1 row. If more than one row is legitimately possible, use IN, EXISTS, or add a LIMIT with a deliberate ORDER BY so the choice is defined rather than arbitrary. - Why they ask: A query that works on test data and dies in production; interviewers like that shape.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.