Краткий ответ

Проверка на отсутствие значения через NOT IN при наличии хотя бы одного NULL в списке может дать не TRUE, а UNKNOWN для каждой проверяемой строки. Условие WHERE оставляет только строки с результатом TRUE, поэтому результатом может стать пустой набор.

Причина — трёхзначная логика SQL: сравнение с неизвестным значением не является ни истинным, ни ложным.

Исторический контекст

Реляционная модель оперирует значениями отношений, но в практических базах данных часто требуется представить отсутствующее, неизвестное или пока не определённое значение. SQL использует для этого специальный маркер NULL.

Чтобы не трактовать неизвестное значение как обычное равенство или неравенство, SQL применяет значения логики TRUE, FALSE и UNKNOWN. Это позволяет отличать «условие ложно» от «невозможно установить истинность условия».

Постановка проблемы

Пусть запрос исключает записи, чьи идентификаторы входят в результат подзапроса. Если подзапрос вернул хотя бы один NULL, проверка отсутствия значения становится неопределённой даже для идентификатора, которого фактически нет среди известных значений.

Риск особенно велик при очистке данных, миграциях и работе с внешними ключами: один неожиданный NULL превращает корректную на вид фильтрацию в пустой результат. Ошибка может выглядеть как отсутствие данных, хотя настоящая причина — неизвестность логического условия.

Подробное решение

Логически проверка x NOT IN (a, b, NULL) эквивалентна отрицанию цепочки сравнений: NOT (x = a OR x = b OR x = NULL). Сравнение x = NULL даёт UNKNOWN, потому что неизвестно, равно ли x неизвестному значению.

Если x не равно a и b, выражение внутри отрицания имеет результат FALSE OR UNKNOWN, то есть UNKNOWN. Отрицание UNKNOWN снова даёт UNKNOWN. Если хотя бы одно сравнение истинно, вся проверка может стать истинной до учёта неопределённого элемента, но для обычного отсутствующего значения это не происходит.

WHERE пропускает только строки, для которых предикат равен TRUE. И FALSE, и UNKNOWN отбрасываются.

SELECT id
FROM customers
WHERE id NOT IN (
  SELECT customer_id
  FROM blocked_customers
);

Если blocked_customers.customer_id содержит NULL, такой запрос может не вернуть ни одной строки. Безопасная альтернатива — NOT EXISTS, где сравниваются конкретные пары строк, а наличие отдельного NULL не делает все остальные проверки неопределёнными:

SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_customers AS b
  WHERE b.customer_id = c.id
);

Ещё один вариант — явно исключить NULL из подзапроса. Это корректно, если по смыслу NULL не должен участвовать в списке запрещённых идентификаторов. Выбор между фильтрацией NULL и NOT EXISTS зависит от семантики данных; слепая замена одного подхода другим может скрыть нарушение целостности.

Ситуация из практики

В сервисе рассылок нужно выбрать клиентов, не попавших в список заблокированных. В таблице блокировок после неудачной миграции появилась строка с неизвестным идентификатором, и фильтр на основе NOT IN перестал возвращать клиентов.

Рассматривались два варианта. Первый — добавить во внутренний запрос условие исключения NULL: это минимальное изменение и сохраняет исходную структуру, но требует помнить о семантике неизвестных значений. Второй — перейти на NOT EXISTS: он явно выражает проверку отсутствия связанной строки и устойчив к постороннему NULL, но может потребовать проверки плана выполнения и индексов.

Выбрали NOT EXISTS, потому что задача была именно проверкой отсутствия соответствующей блокировки, а не сравнением со списком значений. После добавления индекса на идентификатор клиента запрос стал предсказуемым по смыслу, а проблема с отдельными NULL перестала влиять на все строки.

Что кандидаты часто упускают

Почему сравнение с NULL не даёт FALSE, а даёт UNKNOWN?

NULL не является обычным значением, обозначающим конкретный объект. Выражение вроде x = NULL не может установить равенство или неравенство, поэтому его результат — UNKNOWN. Для проверки отсутствия значения применяют IS NULL или IS NOT NULL, а не обычные операторы сравнения.

Почему NOT EXISTS не страдает от NULL во внешнем списке?

NOT EXISTS проверяет, существует ли хотя бы одна строка, удовлетворяющая коррелированному условию. Строка с NULL обычно не удовлетворяет сравнению b.customer_id = c.id, но не превращает проверки других строк в неизвестный общий результат. Поэтому отсутствие совпадения остаётся фактом отсутствия, а не результатом отрицания неопределённого списка.

Достаточно ли объявить столбец подзапроса NOT NULL, чтобы использовать NOT IN?

Для рассматриваемого источника это устраняет главный риск: подзапрос не сможет вернуть NULL. Но надёжность зависит от всего выражения и модели данных: NULL может появиться из вычисления, преобразования или другого источника, если ограничение относится не к фактическому результату запроса. Кроме того, NOT IN сохраняет особую семантику при NULL во внешнем значении, поэтому его следует применять только при явно подтверждённой обработке неопределённых значений.