EXISTS and NOT EXISTS

EXISTS asks a simpler question than IN: does the subquery find at least one row? If it does, EXISTS is true; if the subquery finds nothing, it's false. Which customers have had an order refunded?

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT *
    FROM orders AS o
    WHERE o.customer_id = c.id AND o.status = 'refunded'
)
ORDER BY c.name;

Look at the condition of the subquery: o.customer_id = c.id. The subquery refers to c, the table of the outer query. Such a subquery is called correlated. It can't run on its own (it would end with no such column: c.id), because it depends on the row the outer query is deciding about. Imagine that the database runs it again for every customer. For Emma Clark, c.id is 1, and the subquery looks for refunded orders of customer 1. It finds none, so Emma isn't in the result. For Jack Harris, it finds order 7, so he is.

The subqueries of the previous exercises ran just once, and their result was the same for every row. A correlated subquery can give a different answer for each row. (The database may in fact run it in a smarter way, but the result is the same as if it ran it for every row.)

What the subquery shows doesn't matter: EXISTS only checks whether there's a row. That's why programmers write SELECT * there, or SELECT 1.

NOT EXISTS is true when the subquery finds nothing. These are the customers who haven't recommended the shop to anyone, the question that fell into the trap with NOT IN:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT *
    FROM customers AS r
    WHERE r.referred_by = c.id
)
ORDER BY c.name;

NOT EXISTS has no NULL trap: a customer whose referred_by is NULL simply never matches the condition r.referred_by = c.id. The outer query and the subquery use the same table here, so the aliases c and r are a must: they tell which customer is which.

Typical mistake: forgetting the condition that links the subquery to the outer row. WHERE EXISTS (SELECT * FROM orders WHERE status = 'refunded') asks whether there's any refunded order at all. There is, so the condition is true for every customer, and the query shows all 14 of them.