Correlated Subqueries & EXISTS

The subqueries in the last lesson ran once, independently of the outer query. A correlated subquery refers to the current row of the outer query, so it's evaluated for each row. Combined with EXISTS, it answers "is there at least one…?" questions cleanly and efficiently.

A correlated subquery

SELECT e.first_name, e.department_id, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employees AS x
  WHERE x.department_id = e.department_id
)
ORDER BY e.department_id;

e.department_id inside the subquery comes from the outer row. For each employee, the database calculates their own department's average and compares. (Olivia's department is NULL, so her comparison is unknown and she's excluded.)

Correlated subqueries in the select list

SELECT c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) AS orders,
       (SELECT MAX(o.order_date) FROM orders AS o WHERE o.customer_id = c.id) AS last_order
FROM customers AS c
ORDER BY orders DESC, c.name
LIMIT 5;

Each customer gets their own count and latest date. This is easy to read, but each subquery runs per row; on large tables a join with GROUP BY is often faster.

EXISTS

SELECT c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.id AND o.status = 'pending'
);

EXISTS is true if the subquery returns at least one row. What the subquery selects doesn't matter — SELECT 1 is the convention. The database can stop searching as soon as it finds one match.

NOT EXISTS

SELECT p.name
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id
);

Products never ordered. NOT EXISTS is the safest way to express "has no matching rows": unlike NOT IN, it isn't affected by NULLs.

EXISTS vs IN vs JOIN

A JOIN can duplicate rows when there are several matches (Ana would appear once per order); EXISTS never does.

"For all" questions

SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id)
  AND NOT EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.id AND o.status <> 'delivered'
  );

Customers whose orders have all been delivered — rephrased as "has orders, and has no order that isn't delivered". Double negatives like this are the standard SQL way to express "every".

ANY and ALL

SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

PostgreSQL, MySQL, SQL Server and Oracle support > ALL (subquery) (greater than every value) and = ANY (subquery) (equal to at least one, like IN). SQLite doesn't, but MAX and MIN do the same job:

SELECT name, price FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category = 'Books');

Try it yourself

Find departments that have at least one employee earning over 80,000.