Correlated Subqueries and EXISTS

The subqueries in the last lesson ran once. A correlated subquery refers to the outer query's current row, so it's evaluated for each row – "employees who earn more than the average of their own department". Correlated subqueries power EXISTS and NOT EXISTS, the safest way to ask "is there a related row?".

Correlated subqueries

-- Employees paid above the average of their own department
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employees x
  WHERE x.department = e.department      -- refers to the outer row
)
ORDER BY e.department, e.salary DESC;

+-------------+-------------+--------+
| name        | department  | salary |
+-------------+-------------+--------+
| Rohit Mehta | Engineering | 180000 |
| Kavya Reddy | HR          |  90000 |
| Farhan Ali  | Sales       | 140000 |
+-------------+-------------+--------+

Management has one person, whose salary equals the department average, so no one there is "above average".

The latest order per customer

SELECT o.customer_id, o.order_id, o.order_date
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id;

+-------------+----------+------------+
| customer_id | order_id | order_date |
+-------------+----------+------------+
|           1 |      109 | 2025-10-21 |
|           2 |      107 | 2025-10-03 |
|           3 |      112 | 2025-11-06 |
|           4 |      111 | 2025-11-05 |
|           5 |      106 | 2025-09-15 |
|           6 |      108 | 2025-10-10 |
|           7 |      110 | 2025-11-02 |
+-------------+----------+------------+

"Greatest row per group" is a very common task; window functions (chapter 7) solve it too, often more efficiently.

EXISTS and NOT EXISTS

EXISTS (subquery) is true if the subquery returns at least one row. It doesn't matter what it selects – SELECT 1 is the convention – and the database can stop at the first match.

-- Categories that have at least one product in stock
SELECT cat.name
FROM categories cat
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.category_id = cat.category_id AND p.stock > 0
)
ORDER BY cat.name;

+-------------+
| name        |
+-------------+
| Books       |
| Electronics |
| Stationery  |
+-------------+

-- Customers who have never placed an order
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

+------------+
| name       |
+------------+
| Vikram Das |
+------------+

The NOT IN + NULL trap

Which categories have no products? This looks right but returns nothing:

SELECT name FROM categories
WHERE category_id NOT IN (SELECT category_id FROM products);

Empty set

The subquery's list contains a NULL (the Desk Lamp). 4 NOT IN (1, 2, 3, NULL) means 4 <> 1 AND 4 <> 2 AND 4 <> 3 AND 4 <> NULL – and the last part is UNKNOWN, so the whole condition is never TRUE. NOT EXISTS doesn't have this problem:

SELECT cat.name FROM categories cat
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = cat.category_id);

+---------+
| name    |
+---------+
| Fitness |
+---------+

Subquery or join?

Modern optimisers often turn IN/EXISTS into joins internally, so choose the form that states your intent most clearly – then check the plan with EXPLAIN (chapter 9) if speed matters.

-- JOIN repeats customers once per order; EXISTS lists each customer once
SELECT c.name FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE c.city = 'Pune';
SELECT c.name FROM customers c WHERE c.city = 'Pune'
  AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

+------------+
| name       |
+------------+
| Asha Verma |
| Asha Verma |
| Asha Verma |
+------------+
+------------+
| name       |
+------------+
| Asha Verma |
+------------+

Practice

Summary