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

Запрос может вернуть ни одной строки, потому что подзапрос содержит NULL. Для значения, не равного 2, проверка c.id NOT IN (2, NULL) даёт не TRUE, а UNKNOWN, поэтому строка отбрасывается условием WHERE.

Надёжный вариант — заменить NOT IN на NOT EXISTS, явно сопоставляя идентификаторы. Это сохраняет нужную семантику даже при наличии NULL в чёрном списке.

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

SQL поддерживает не только значения «истина» и «ложь», но и состояние неизвестно (UNKNOWN). Оно понадобилось для корректной работы с NULL, который означает отсутствие известного значения, а не обычное значение, равное нулю или пустой строке.

Трёхзначная логика позволяет не делать необоснованный вывод о сравнении с неизвестным значением. Однако при фильтрации это часто становится источником ошибок: WHERE оставляет только строки, для которых условие имеет значение TRUE.

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

NOT IN логически разворачивается в цепочку сравнений с отрицанием. В данном случае проверка для клиента с идентификатором 1 эквивалентна условию 1 <> 2 AND 1 <> NULL.

Первое сравнение истинно, но сравнение с NULL неизвестно. В результате всё выражение имеет значение UNKNOWN, а не TRUE. То же происходит с клиентом 3, поэтому ожидаемые идентификаторы 1 и 3 не попадают в результат.

Такая ошибка опасна при построении выборок для рассылок, расчёте доступных клиентов или исключении мошеннических аккаунтов: результат может оказаться пустым либо неполным без явной ошибки выполнения.

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

IN возвращает TRUE, если найдено равное значение, и UNKNOWN, если совпадения нет, но среди вариантов есть NULL. Следовательно, NOT IN от UNKNOWN также получает UNKNOWN; SQL не превращает его в TRUE автоматически.

Безопасный вариант:

WITH customers(id) AS (
  VALUES (1), (2), (3)
), blacklist(id) AS (
  VALUES (2), (NULL)
)
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blacklist AS b
  WHERE b.id = c.id
);

Для каждого клиента NOT EXISTS проверяет наличие строки с равным идентификатором. Строка с NULL не удовлетворяет сравнению b.id = c.id, но сама по себе не делает результат подзапроса неизвестным: если совпадение с конкретным клиентом не найдено, подзапрос не существует, и NOT EXISTS возвращает TRUE.

Другой вариант — исключить NULL из подзапроса: WHERE b.id IS NOT NULL. Но это требует помнить о защитном условии во всех подобных запросах. NOT EXISTS обычно яснее выражает намерение и не зависит от наличия NULL в возвращаемом столбце.

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

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

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

Команда рассматривала три решения. Удаление ошибочной строки было быстрым, но не защищало от повторения проблемы. Добавление IS NOT NULL в подзапрос исправляло текущий запрос, однако требовало дисциплины во всех аналогичных местах. Переход на NOT EXISTS явно задавал проверку отсутствия совпадения и не зависел от посторонних NULL.

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

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

1. Дополнительный вопрос: Что вернёт условие id NOT IN (2, NULL) для id = 2?

Оно вернёт FALSE, потому что найдено точное совпадение с 2. Наличие NULL не меняет уже установленный отрицательный результат: для IN достаточно одного истинного сравнения, а для NOT IN найденное совпадение означает, что отрицание ложно.

2. Дополнительный вопрос: Поможет ли заменить NOT IN на NOT IN (SELECT COALESCE(id, -1) ...)?

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

3. Дополнительный вопрос: Чем в этой ситуации отличается LEFT JOIN ... WHERE b.id IS NULL от NOT EXISTS?

При соединении по b.id = c.id вариант с LEFT JOIN обычно даёт тот же результат: клиент без совпавшей строки имеет NULL в полях b, а условие b.id IS NULL его сохраняет. Однако NOT EXISTS прямо выражает проверку отсутствия совпадения и не создаёт промежуточные строки для множественных совпадений, поэтому часто проще для анализа намерения и защиты от ошибок в более сложных соединениях.