Почему NOT IN с NULL возвращает пустой результат

SQL Studio / Разборы SQL / Почему NOT IN с NULL возвращает пустой результат

Причина: SQL использует трёхзначную логику — TRUE, FALSE и UNKNOWN. Если справа в NOT IN есть NULL, для не совпавшего значения предикат не становится истинным. Условие WHERE такую строку не оставляет.

1. Воспроизводим ошибку

Список исключённых ID зададим через VALUES, чтобы пример работал сразу в песочнице:

WITH excluded(customer_id) AS (VALUES (1), (2), (NULL))
SELECT c.customer_id, c.name
FROM customers AS c
WHERE c.customer_id NOT IN (SELECT customer_id FROM excluded)
ORDER BY c.customer_id;

Результат — пустая таблица. Клиент №3 не равен ни 1, ни 2, но сравнение с NULL не доказывает, что он не входит в список.

2. Решение через NOT EXISTS

Вместо проверки принадлежности списку спрашиваем, существует ли хотя бы одна строка с совпадающим ID:

WITH excluded(customer_id) AS (VALUES (1), (2), (NULL))
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM excluded AS e
  WHERE e.customer_id = c.customer_id
)
ORDER BY c.customer_id;

NULL в списке не равен идентификатору клиента и не мешает NOT EXISTS. Для антисоединений по ключу этот вариант обычно проще объяснить и проверить.

3. Когда NOT IN всё-таки подходит

Если левая сторона гарантированно не NULL (у нас customer_id — первичный ключ), можно явно исключить NULL справа:

WITH excluded(customer_id) AS (VALUES (1), (2), (NULL))
SELECT c.customer_id, c.name
FROM customers AS c
WHERE c.customer_id NOT IN (
  SELECT customer_id FROM excluded WHERE customer_id IS NOT NULL
)
ORDER BY c.customer_id;

Результат снова Вера и Данил. Но если слева допускается NULL, вам всё ещё нужно решить, считать ли неопределённый идентификатор отсутствующим. Универсально переносить условие без анализа данных нельзя.

4. Альтернатива: LEFT JOIN ... IS NULL

Для задачи «покажите клиентов без единого заказа» проверяем отсутствие реальной строки заказа:

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Результат — Данил. В условии IS NULL используем ненулевой первичный ключ правой таблицы, а не поле, которое может быть пустым у существующей записи.

Смотрите также LEFT JOIN и условия WHERE и COUNT DISTINCT и NULL.

Справка по SQL-логике: SQLite — IN, NOT IN, NULL.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.