Subqueries and EXISTS

A subquery is a query inside a query. Interviews care about three distinctions: where it sits, whether it references the outer query, and whether a join would be better.

Where subqueries go

-- In WHERE, as a scalar: exactly one row, one column
SELECT first_name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- In WHERE, as a list
SELECT first_name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE region = 'EMEA');

-- In FROM, as a derived table (must be aliased)
SELECT d.department, d.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees GROUP BY department
) d
WHERE d.avg_salary > 80000;

-- In SELECT, as a scalar per row
SELECT first_name,
       (SELECT COUNT(*) FROM orders o WHERE o.employee_id = e.id) AS order_count
FROM employees e;

A scalar subquery returning more than one row is a runtime error, not a silent wrong answer — but returning zero rows yields NULL, which then propagates through any comparison. That is the failure mode to watch for.

Correlated subqueries

An uncorrelated subquery is independent and can be run once. A correlated one references a column from the outer query, so conceptually it runs per outer row:

-- Correlated: the inner query depends on e.department
SELECT first_name, salary FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees
    WHERE department = e.department          -- ← the correlation
);

That reads well and is O(rows × inner cost) in the naive plan. Modern optimisers often rewrite it, but the version an interviewer wants to hear about is the window function:

SELECT first_name, salary FROM (
    SELECT first_name, salary,
           AVG(salary) OVER (PARTITION BY department) AS dept_avg
    FROM employees
) t
WHERE salary > dept_avg;

One pass instead of one per row.

EXISTS versus IN

-- EXISTS: stop at the first match; the SELECT list is irrelevant
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- IN: build the value list, then test membership
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Both are usually optimised into the same semi-join, so pick on semantics:

`NOT IN` with a NULL in the subquery returns zero rows — the trap explained in

filtering. NOT EXISTS has no such problem, which is why it is the default choice for anti-joins.

SELECT 1 inside EXISTS is conventional: the engine never evaluates the select list, so SELECT * is equally fast and SELECT 1 just signals intent.

The Nth highest without window functions

The classic interview question, and worth being able to answer both ways:

-- Correlated count: how many distinct salaries are strictly greater?
SELECT DISTINCT salary FROM employees e
WHERE 2 = (
    SELECT COUNT(DISTINCT salary) FROM employees
    WHERE salary > e.salary
);        -- exactly two above it ⇒ third highest

-- Or without any subquery at all
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

The correlated version is the one asked when the interviewer says "without LIMIT" or "without window functions". Related: "the highest salary without MAX()" is just ORDER BY salary DESC LIMIT 1, or "the salary with nothing greater than it":

SELECT salary FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees WHERE salary > e.salary);

When to rewrite as a join

Subqueries in SELECT that fetch one value per row are the usual candidate — one correlated scalar subquery per output column becomes several passes over the same table:

-- Two correlated subqueries: two passes
SELECT e.name,
       (SELECT COUNT(*) FROM orders o WHERE o.employee_id = e.id) AS orders,
       (SELECT SUM(total) FROM orders o WHERE o.employee_id = e.id) AS revenue
FROM employees e;

-- One aggregate join: one pass
SELECT e.name, COALESCE(o.orders, 0), COALESCE(o.revenue, 0)
FROM employees e
LEFT JOIN (
    SELECT employee_id, COUNT(*) AS orders, SUM(total) AS revenue
    FROM orders GROUP BY employee_id
) o ON o.employee_id = e.id;

The LEFT JOIN plus COALESCE preserves employees with no orders — an inner join would drop them, which is the subtle behaviour change to watch for when rewriting.

Mistakes that cost the interview

• `NOT IN` over a nullable column.
• Unaliased derived tables. Most engines require the alias.
• Scalar subquery returning multiple rows, which errors at run time.
• Repeating the same correlated subquery for several columns instead of joining once.
• Forgetting that a correlated subquery sees the outer alias — that is what makes it

correlated, and forgetting it produces a silently uncorrelated query that compares against a global aggregate.

Practise these on the SQL sheet.