Level 2 · Customers who returned something

“Customers who did X” is a semi-join: keep rows from one table when a related row exists in another, without bringing that table’s columns along. There are three ways to write it, and they do not behave the same.

JOIN creates duplicates

SELECT c.customer_id, c.first_name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS i ON i.order_id = o.order_id
JOIN returns AS r ON r.order_item_id = i.order_item_id;

A customer with three returns appears three times. You could add DISTINCT, but that is the “hide the fan-out” habit from chapter 1. It works here and quietly breaks the moment someone adds a SUM.

EXISTS

SELECT c.customer_id, c.first_name, c.last_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN returns AS r ON r.order_item_id = i.order_item_id
  WHERE o.customer_id = c.customer_id
);

EXISTS is true if the subquery returns at least one row. Each customer is tested once and kept once, however many returns they have. The SELECT 1 is a convention; EXISTS ignores what the subquery selects. The WHERE o.customer_id = c.customer_id line makes it correlated: it ties the inner query to the current outer customer. Forget it and EXISTS is true for everyone as soon as any return exists.

IN

WHERE c.customer_id IN (
  SELECT o.customer_id
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN returns AS r ON r.order_item_id = i.order_item_id
)

Same result, and many people find it easier to read. Duplicates in the list do not matter.

NOT IN and the NULL trap

The negative forms are where they differ. x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL. The last comparison is unknown, so the whole condition is never true and no rows come back. If the subquery column can contain even one NULL, NOT IN returns nothing:

-- returns zero rows: web_sessions.customer_id is NULL for anonymous visitors
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM web_sessions);

NOT EXISTS has no such problem, because it only asks whether a matching row exists:

WHERE NOT EXISTS (
  SELECT 1 FROM web_sessions AS s WHERE s.customer_id = c.customer_id
)

Rule of thumb: IN and EXISTS are interchangeable for “has a match”. For “has no match”, prefer NOT EXISTS or the LEFT JOIN anti-join from chapter 5.

Performance

Old advice says EXISTS is faster than IN, or the reverse. Modern optimizers in Postgres, DuckDB, BigQuery and MySQL 8 turn both into the same semi-join plan most of the time. Choose for correctness and readability.

Interview note

“Find customers who never visited the site” with a NOT IN that returns zero rows is a favorite trick question. Explaining the NULL behavior, and fixing it with NOT EXISTS, is the answer that stands out.