NOT IN and NULL

NOT IN is the opposite of IN: it keeps the rows whose value is not in the list. These are the games nobody has ever ordered:

SELECT title
FROM games
WHERE id NOT IN (SELECT game_id FROM order_items)
ORDER BY title;

You'd get the same result with LEFT JOIN … IS NULL from lesson 6, but this query reads almost like the question itself.

Now a trap that catches even experienced programmers. Which customers haven't recommended the shop to anyone? The column referred_by holds the ID of the customer who recommended the shop, so the customers who recommended nobody are those whose id isn't in that column:

SELECT name
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers)
ORDER BY name;

The result is empty, although Henry Scott, for example, has clearly recommended the shop to nobody. The reason is the list. Many customers came on their own, so their referred_by is NULL, and the subquery returns NULL, 1, 1, NULL, 3, 2, NULL and so on.

id NOT IN (1, 3, NULL) means “id isn't 1, and isn't 3, and isn't NULL”. And as you know from lesson 3, a comparison with NULL is never true: NULL is an unknown value, and whether 12 differs from an unknown value is unknown too. So the condition isn't true for any customer:

SELECT 12 NOT IN (1, 3) AS without_null,
       12 NOT IN (1, 3, NULL) AS with_null;

1 means true. NULL means “unknown”, and WHERE keeps only the rows for which the condition is true. One NULL in the list, and NOT IN finds nothing. (IN doesn't have this problem: a value that is in the list is found, whatever else the list holds.)

The fix is to leave NULL out of the list: add WHERE referred_by IS NOT NULL to the subquery. Or use NOT EXISTS, which you'll learn in the next exercise and which doesn't have this trap.

Typical mistake: forgetting about NULL. Whenever you write NOT IN with a subquery, ask yourself whether the subquery's column can be NULL. A primary key such as id never is; a column like referred_by can be. And you get no error message: the query just quietly finds nothing.