Lesson 29 / 37 · Composing queries

EXISTS asks a question per row

The subquery mentions the outer row (o.customer_id = c.id), so it cannot run on its own: it is evaluated once per customer. dequery says so instead of pretending. IN with a subquery, or a join, can often express the same thing.

The query

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

Try NOT EXISTS: the other 8 customers pass, those without a refunded order.

Next: NOT IN with a NULL in the list keeps nothing · All lessons