Subqueries: IN, EXISTS and correlated

Three places a query can contain a query

-- In WHERE: filter against another result
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'FR');

-- In FROM: treat a result as a table
SELECT country, AVG(order_count)
FROM (
  SELECT c.country, COUNT(o.id) AS order_count
  FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.country
) AS per_customer
GROUP BY country;

-- In SELECT: one value per output row
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c;

The third form must return exactly one row and one column, or the query fails at runtime — on the day the data grows a second match, not when you write it.

Correlated means "runs per row"

A subquery that mentions a column from the outer query cannot be computed once. It is re-evaluated for every candidate row:

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees
  WHERE department_id = e.department_id   -- the correlation
);

Remove that last line and the subquery is independent — one average for the whole company, computed once. With it, the question changes to "above their own department's average", and the engine has a per-department average to produce. Modern planners usually rewrite this into a join or a window function; older ones, and any engine given an unindexed column, do not.

EXISTS asks a yes-or-no question

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT 1 is idiomatic and means what it looks like: nothing about the inner row is used, only whether one exists. The engine can stop at the first match instead of collecting them all.

Why EXISTS is the safer default

IN and EXISTS answer the same question until a NULL appears on the inside — and then NOT IN returns nothing at all:

-- Returns zero rows if any department_id in the list is NULL
SELECT * FROM employees
WHERE department_id NOT IN (SELECT id FROM departments);

-- Correct whatever the data contains
SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.department_id
);

NOT IN (1, 2, NULL) becomes x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is unknown, so the whole thing can never be true. NOT EXISTS has no such rule.

When it should have been a join

A subquery in SELECT returns one value. If you need three columns from the other table, three subqueries is three passes — a join is one:

-- Three scans of orders
SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id),
       (SELECT MAX(created_at) FROM orders o WHERE o.customer_id = c.id),
       (SELECT SUM(total) FROM orders o WHERE o.customer_id = c.id)
FROM customers c;

-- One
SELECT c.name, COUNT(o.id), MAX(o.created_at), SUM(o.total)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

The rule of thumb: a subquery to test something, a join to get something. The moment you find yourself writing the same correlated subquery twice in one SELECT, it was a join.