Correlated Subqueries and EXISTS

A correlated subquery refers to the outer query's current row, so it runs (logically) once per row.

Comparing with your own group

Which products cost more than the average of their own category?

SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p.category
);

+---------------------+-------------+-------+
|        name         |  category   | price |
+---------------------+-------------+-------+
| Mechanical Keyboard | Accessories |    89 |
+---------------------+-------------+-------+

For each product p, the subquery averages only products in p's category. Only the Accessories category has more than one product, so the keyboard is the only product above its category average (55.5).

EXISTS

EXISTS (subquery) is true if the subquery returns any row. It reads naturally for "has at least one":

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

+--------------+
|     name     |
+--------------+
| Ada Lovelace |
| Grace Hopper |
+--------------+

SELECT 1 is a convention — EXISTS only checks whether rows exist, not what they contain.

NOT EXISTS

NOT EXISTS finds rows with no match, and — unlike NOT IN — handles NULLs safely:

SELECT p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE oi.product_id = p.id AND o.status = 'delivered'
);

+-----------------------------+
|            name             |
+-----------------------------+
| Noise-Cancelling Headphones |
| Webcam                      |
+-----------------------------+

"Products that have never been part of a delivered order."

Joins, IN, or EXISTS?

These often express the same question. Modern optimizers usually run them equally fast, so choose the clearest:

• Need columns from both tables → JOIN.
• "Rows that have / don't have a related row" → EXISTS / NOT EXISTS.
• Short fixed lists → IN.

Practice

List employees who manage at least one person, using EXISTS.

SELECT m.name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.id);

+-------+
| name  |
+-------+
| Sofia |
| Chen  |
+-------+