SQL Subquery Interview Questions

Q1: Correlated vs non-correlated subquery

A non-correlated subquery is fully independent — it could run on its own and be pasted in as a literal value. A correlated subquery references a column from the outer query, so it logically has to be re-evaluated once per outer row.

-- Non-correlated: runs once, independent of the outer query
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated: references e from the outer query, re-evaluated per row
SELECT name FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE department = e.department
);

The second version answers a different, per-row question — "does this employee earn more than the average in their own department," not the company-wide average. Full depth in Correlated Subqueries Explained Simply.

Q2: The NOT IN with NULL trap

SELECT name FROM employees
WHERE department_id NOT IN (
  SELECT department_id FROM departments WHERE is_active = false
);
-- one row in departments has department_id = NULL

A single NULL anywhere in the subquery's result list poisons the entire NOT IN. NOT IN (1, 2, NULL) expands conceptually to value <> 1 AND value <> 2 AND value <> NULL — and value <> NULL is always NULL, not true or false, so the whole AND chain can never evaluate to true for any row. The safe fix is filtering NULLs out of the subquery explicitly, or using NOT EXISTS instead, which doesn't have this failure mode. See NULL Handling in SQL for the underlying three-valued logic.

Q3: IN vs EXISTS

-- IN: compares against the full list of subquery values
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS: only checks whether a matching row exists at all
SELECT name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Both return the same customers here, but EXISTS is generally the safer default, especially negated as NOT EXISTS, because it never runs into the NOT IN/NULL failure from Q2 — it's checking for the existence of a matching row, not comparing against a literal list that might contain NULL.

Q4: What happens if a scalar subquery returns 2 rows?

SELECT name,
  (SELECT department_name FROM departments WHERE id = e.department_id) AS dept
FROM employees e;
-- if department_id is duplicated in departments, this fails at runtime

A subquery used where a single value is expected — in a SELECT list, or beside =, >, etc. — is a scalar subquery, and it must return exactly one row and one column. If the underlying data ever produces two matching rows, the query doesn't silently pick one; it fails with a runtime error, which is usually the correct, safer behavior for catching a data integrity problem early.

Q5: Subquery in FROM (derived table)

SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_averages
WHERE avg_salary > 80000;

A subquery inside FROM is called a derived table, and it must be given an alias (dept_averages here) — every major database requires this, since the derived table is otherwise unnamed and unreferenceable in the outer query. This is also how alias scope plays out for a whole subquery rather than just a column.

Q6: CTE vs subquery — when to use what

A CTE is generally the better choice when the same derived result needs to be referenced more than once in a single query, when naming the intermediate step improves readability, or when recursion is required — a subquery has no equivalent to a recursive CTE. For a full side-by-side comparison, see CTEs vs Subqueries – When to Use What and SQL Recursive CTEs With Examples.