Subqueries that run once per row — powerful, occasionally slow, and the safe answer to "not in".

EXISTS and Correlated Subqueries

The subqueries in the last lesson were independent: run once, produce a value, done. A correlated subquery refers to the outer query and is conceptually re-evaluated for every outer row.

SQL
SELECT e.first_name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary)
                  FROM employees x
                  WHERE x.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salary DESC;

"Everyone paid above the average for their own department." The inner query mentions e.dept_id from the outer query, so it cannot run on its own — the department changes with each employee being considered.

Compare with lesson 24's version, which compared against the company-wide average. One word of difference in the inner WHERE, a completely different question.

EXISTS — does a matching row exist?

EXISTS takes a subquery and returns true if it produces any row at all. What the subquery selects is irrelevant, which is why SELECT 1 is the convention:

SQL
SELECT c.name AS has_ordered
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

SQL
SELECT c.name AS has_never_ordered
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

EXISTS can stop at the first matching row — it does not need to count or collect anything — so SELECT 1, SELECT * and SELECT id perform identically. Use SELECT 1 because it says "I do not care about the values".

Why NOT EXISTS beats NOT IN

NOT EXISTS asks "is there no matching row?", which is a question with a definite answer even when the values are NULL. NOT IN asks "is this value different from every value in the list?", which is unanswerable if any of them is unknown.

SQL
SELECT COUNT(*) AS found_by_not_in
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

SQL
SELECT COUNT(*) AS found_by_not_exists
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

Zero against one. Same intent, same data, different answers — and only one of them is right. Make NOT EXISTS the default and the whole class of bug disappears.

The three ways to write "rows with no match"

All three are correct here, and each reads differently:

SQL
SELECT d.name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

NOT EXISTS — states the intent most directly, and is NULL-safe.
LEFT JOIN … IS NULL — the classic; fine, slightly indirect, and it materialises the join before discarding most of it.
NOT IN — avoid on anything nullable.

Modern planners generally treat NOT EXISTS and the anti-join form the same way, so pick for readability, not for speed.

Correlated subqueries in the SELECT list

Useful and easy to overuse:

SQL
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders
FROM customers c
ORDER BY orders DESC, c.name;

Every customer, including those with none — a GROUP BY over an inner join would have dropped them. It reads well, and it is one subquery per output row.

With one column that is fine. With five such columns you have five passes over orders, where a single LEFT JOIN … GROUP BY would do one:

SQL
SELECT c.name,
       COUNT(o.order_id)                 AS orders,
       ROUND(COALESCE(SUM(o.amount), 0), 2) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY spent DESC, c.name;

Same shape of answer, one pass, and it scales. The COALESCE matters here for the reason lesson 15 gave: SUM over no rows is NULL, and a customer who never ordered should show 0.00, not blank.

The performance reality

"Correlated subqueries run once per row" is the mental model, not a promise about what the database does. Planners frequently decorrelate them into joins or semi-joins, and then the difference vanishes. But they are the shape most likely to degrade badly on a large outer table, particularly with no index on the inner join column.

The practical guidance:

One or two correlated columns on a modest result: fine, and clearer than a join.
A correlated subquery over a large outer table, or several of them: rewrite as an aggregate join or a CTE.
Never guess. EXPLAIN shows you what the planner actually chose.

Common mistakes

NOT IN on a nullable column — the bug this lesson exists to retire.
Forgetting the correlation — drop WHERE x.dept_id = e.dept_id and every row silently compares against the global average.
SELECT COUNT(*) > 0 instead of EXISTS — counts every match when it only needs to find one.
Stacking correlated subqueries in SELECT — one pass per column.

Interview question

When would you use EXISTS instead of IN?

When the subquery can produce NULLs — NOT IN returns nothing then — and when you only need to know whether a match exists rather than collect the values. EXISTS can also short-circuit at the first hit.

Check yourself

Find customers who have never placed an order, two different ways.
What makes a subquery correlated?
Why is SELECT 1 conventional inside EXISTS?