SQL Subqueries Interview Questions and Answers

Subqueries are where SQL interviews separate people who memorized syntax from people who understand how a query executes. Almost every analyst, backend and data-engineering interview includes at least one "rewrite this with a subquery" or "why does this return nothing" question, because the answers expose whether you understand result sets, NULL logic and the optimizer.

The examples below use two familiar tables: employees(id, name, dept_id, salary, manager_id) and departments(id, name, location). Every statement is standard SQL and runs on PostgreSQL, MySQL 8+, SQL Server and Oracle unless noted.

What is a subquery, and where can it appear?

A subquery is a query nested inside another statement. It can appear in the SELECT list (as a scalar value), in FROM (as a derived table), in WHERE or HAVING (as a filter), and in INSERT/UPDATE/DELETE. Its role depends on where it sits.

The position determines what shape it must return. A subquery in a scalar context must return one row and one column; a subquery feeding IN may return many rows of one column; a subquery in FROM returns a full table you then query again.

SELECT name,
       (SELECT name FROM departments d WHERE d.id = e.dept_id) AS dept
FROM employees e;

Answer this by naming the four positions and giving one example each — it signals you think in terms of what the subquery returns, not just where the parentheses go.

Q1. Correlated vs non-correlated subquery — what is the difference?

A non-correlated subquery is self-contained: it can run on its own and executes once. A correlated subquery references a column from the outer query, so logically it re-runs for each outer row.

Non-correlated example — employees earning more than the company average:

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

Correlated example — employees earning more than their own department's average:

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM employees x
                  WHERE x.dept_id = e.dept_id);

The tell is the reference to e.dept_id inside the inner query. Say out loud that the correlated form is conceptually per-row (optimizers often rewrite it internally), which is why it is the honest way to express "compare each row to its own group".

Interview note: Follow-up: "how would you write the correlated version with a window function?" AVG(salary) OVER (PARTITION BY dept_id) — knowing both forms is what marks an intermediate candidate.

Q2. IN vs EXISTS — when do you use each?

IN tests membership in a value list; EXISTS tests whether a correlated subquery returns any row at all. Use EXISTS for existence checks and when NULLs may appear; IN is fine for small, non-null value sets.

-- EXISTS: departments that have at least one employee
SELECT d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

EXISTS can stop at the first matching row, and it never trips on NULLs. IN materializes the whole list. On modern optimizers the performance is often identical, so lead with correctness: EXISTS is NULL-safe, IN is not.

Interview note: Trap: "does SELECT 1 vs SELECT * inside EXISTS matter?" No — EXISTS only checks row existence, so the select list is ignored. Choosing 1 is convention, not optimization.

Q3. Why can NOT IN return zero rows unexpectedly?

Because if the subquery returns a NULL, NOT IN compares each outer value against that NULL, which yields UNKNOWN, and UNKNOWN filters the row out. One NULL in the list poisons the entire result.

-- Dangerous: if any manager_id is NULL, this returns nothing
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- Safe rewrite with NOT EXISTS
SELECT e.name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id);

This is the single most-asked subquery trap. NOT EXISTS handles NULLs correctly because it asks "does a matching row exist?" rather than doing value comparisons. If you must keep NOT IN, add WHERE manager_id IS NOT NULL inside the subquery.

Interview note: Follow-up: "why is IN not affected the same way?" With IN, a NULL just fails to match and the row can still qualify on a real value; with NOT IN, the NULL makes the negation UNKNOWN, which is fatal.

Q4. What is a scalar subquery, and what happens if it returns two rows?

A scalar subquery returns exactly one row and one column and can be used wherever a single value is expected. If it returns more than one row at runtime, the database throws an error.

SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employees) AS above_avg
FROM employees;

The interview-grade point: correctness depends on data. A scalar subquery like (SELECT id FROM departments WHERE name = 'Sales') works only if name is unique. If a duplicate appears later, the query starts failing in production — which is why you either guarantee uniqueness with a constraint or use LIMIT 1/MAX.

Interview note: Trap: "what does a scalar subquery return when it matches no rows?" NULL, not an error. Empty is fine; multiple rows is the error case.

Q5. Subquery in FROM (derived table) vs a CTE — what is the difference?

Both name an intermediate result. A derived table is a subquery in the FROM clause with an alias; a CTE (WITH) names it up front, can be referenced multiple times, and reads top-to-bottom. Functionally they are usually equivalent.

-- Derived table
SELECT dept_id, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal
      FROM employees GROUP BY dept_id) t
WHERE avg_sal > 50000;

-- Same logic as a CTE
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
)
SELECT dept_id, avg_sal FROM dept_avg WHERE avg_sal > 50000;

Prefer a CTE when the same subresult is used more than once or when nesting hurts readability; it is the same optimizer input, just clearer. Mention that CTEs can also be recursive — the standard follow-up.

Interview note: Follow-up: "are CTEs always materialized?" No — most engines inline them; PostgreSQL inlines non-recursive CTEs since v12 unless you write MATERIALIZED. Assuming materialization is an outdated belief.

Q6. Rewrite a correlated subquery as a JOIN.

Many correlated subqueries are joins in disguise. Rewriting them can be clearer and lets the optimizer pick a set-based plan.

-- Correlated subquery
SELECT e.name
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary)
                  FROM employees x WHERE x.dept_id = e.dept_id);

-- Join to the per-department max
SELECT e.name
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS max_sal
      FROM employees GROUP BY dept_id) m
  ON e.dept_id = m.dept_id AND e.salary = m.max_sal;

Both return the top earner(s) per department. Point out that the join version handles ties identically (both surface all tied rows) and is often easier for the optimizer to parallelize.

Interview note: Follow-up: "which is faster?" On a good optimizer, usually the same plan. Choose for readability first; profile with EXPLAIN before claiming one is faster.

Q7. Can you use a subquery in an UPDATE or DELETE?

Yes — both correlated and non-correlated subqueries are valid in the SET clause and the WHERE clause of DML.

-- Give everyone in loss-making departments a flag
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'Hyderabad');

The senior detail: a correlated subquery in SET lets you pull a matched value from another table per row. Warn that some engines (older MySQL) forbid referencing the target table in a subquery of its own UPDATE — you work around it by wrapping the subquery in another derived table.

Interview note: Trap: "what if the UPDATE subquery returns no match for a row?" With a scalar subquery in SET, that column becomes NULL. Guard it with a WHERE EXISTS so you only touch rows that have a match.

Q8. Subquery vs JOIN vs window function for "top N per group" — how do you choose?

Subquery/JOIN with a per-group aggregate handles "the max/min per group". A window function (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)) handles "the top N per group" cleanly, including ties control.

SELECT name, dept_id, salary
FROM (SELECT name, dept_id, salary,
             ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
      FROM employees) ranked
WHERE rn <= 3;

Explaining why the window function goes inside a derived table — you cannot filter on a window function in the same WHERE, because window functions are evaluated after WHERE — is exactly the depth interviewers want.

Interview note: Follow-up: "ROW_NUMBER vs RANK vs DENSE_RANK for ties?" ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps, DENSE_RANK does not — pick by whether ties should share a rank.

How to prepare

Build the two-table schema above and actually run each query — especially the NOT IN NULL trap, which only becomes memorable when you watch it return zero rows and then fix it with NOT EXISTS. Then practice rewriting every correlated subquery you write as a join and as a window function; being able to move fluidly between the three forms is the intermediate-to-senior signal.

Pair this with the window functions questions — the "top N per group" pattern bridges both — and the aggregate functions set for the GROUP BY logic that subqueries so often wrap. For the fundamentals underneath, the SQL learning path builds the query model these questions test. A focused mock interview on query rewriting is the fastest way to make the correlated-to-join translation automatic.