EXISTS and correlated subqueries

Some subqueries refer to the row the outer query is looking at. These are correlated subqueries: they run once for each outer row.

EXISTS asks "is there at least one matching row?":

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

That's every customer who has never ordered. For each customer, the inner query looks for one of their orders; NOT EXISTS keeps the customer when there are none. (SELECT 1 is a convention: EXISTS only cares whether a row comes back, not what's in it.)

A correlated subquery can also compare a row with its own group:

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

Each product is compared with the average of its own category.

Watch out for NOT IN with NULLs: WHERE id NOT IN (SELECT x FROM t) returns no rows at all if any x is NULL. NOT EXISTS doesn't have this problem, so prefer it.