NOT IN и NULL

NOT IN — противоположность IN: оставляет строки, значения которых нет в списке. Вот игры, которые никто никогда не заказывал:

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

Тот же результат дал бы LEFT JOIN … IS NULL из урока о соединениях, но этот запрос читается почти как сам вопрос.

Теперь ловушка, в которую попадают даже опытные программисты. Какие клиенты никому не рекомендовали магазин? В столбце referred_by лежит идентификатор клиента, который порекомендовал магазин, поэтому клиенты, никого не порекомендовавшие, — это те, чьего id нет в этом столбце:

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

Результат пуст, хотя, например, Henry Scott явно никому не рекомендовал магазин. Причина — в списке. Многие клиенты пришли сами, поэтому их referred_by равен NULL, и подзапрос возвращает NULL, 1, 1, NULL, 3, 2, NULL и так далее.

id NOT IN (1, 3, NULL) означает «id не равен 1, и не равен 3, и не равен NULL». А как вы знаете из урока 3, сравнение с NULL никогда не бывает истинным: NULL — неизвестное значение, и отличается ли 12 от неизвестного значения, тоже неизвестно. Поэтому условие не выполняется ни для одного клиента:

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

1 означает «истина». NULL означает «неизвестно», а WHERE оставляет только строки, для которых условие истинно. Один NULL в списке — и NOT IN ничего не находит. (У IN такой проблемы нет: значение, которое есть в списке, находится, что бы ещё в нём ни лежало.)

Исправить это можно, убрав NULL из списка: добавьте в подзапрос WHERE referred_by IS NOT NULL. Или использовать NOT EXISTS, который вы изучите в следующем упражнении и у которого этой ловушки нет.

Типичная ошибка: забыть про NULL. Каждый раз, когда вы пишете NOT IN с подзапросом, спрашивайте себя, может ли столбец подзапроса содержать NULL. Первичный ключ, такой как id, — никогда; столбец вроде referred_by — может. И сообщения об ошибке вы не получите: запрос просто молча ничего не найдёт.