Почему NOT IN с NULL убирает строки и когда нужен NOT EXISTS

Если справа от NOT IN есть NULL, строка без найденного совпадения может получить не true, а неизвестный результат. WHERE оставляет только true, поэтому ожидаемая строка исчезает. На Mentoring.digital разберём ловушку на самодостаточном запросе PostgreSQL, который не меняет таблицы.

Минимальный пример без рабочей базы

WITH source(id) AS (VALUES (1), (2), (NULL::integer)),
     blocked(id) AS (VALUES (2), (NULL::integer))
SELECT id
FROM source
WHERE id NOT IN (SELECT id FROM blocked);

Для этого набора запрос не возвращает строк. Значение 2 исключается совпадением. Для 1 сравнение с 2 не находит совпадения, но сравнение с NULL не даёт известного ответа. Для NULL слева результат тоже неизвестен.

Это не особенность индекса и не пропажа данных. Проблема в логическом условии. Добавление индекса или изменение сортировки не определит, что означало пропущенное значение в бизнес-задаче.

Вариант с явным исключением NULL

WITH source(id) AS (VALUES (1), (2), (NULL::integer)),
     blocked(id) AS (VALUES (2), (NULL::integer))
SELECT id
FROM source
WHERE id IS NOT NULL
  AND id NOT IN (
    SELECT id FROM blocked WHERE id IS NOT NULL
  );

Здесь ожидаемый результат: 1. Условие явно исключает пропуски и в исходном наборе, и в списке запрета. Это подходит, только если такой смысл соответствует требованиям. Нельзя автоматически подставлять вместо NULL число 0: оно может оказаться настоящим ID.

Когда удобнее NOT EXISTS

WITH source(id) AS (VALUES (1), (2), (NULL::integer)),
     blocked(id) AS (VALUES (2), (NULL::integer))
SELECT s.id
FROM source AS s
WHERE s.id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM blocked AS b WHERE b.id = s.id
  );

Этот вариант спрашивает, существует ли совпадающая строка. Для приведённого набора он тоже оставляет 1. Но удаление s.id IS NOT NULL изменит результат: строка с NULL слева не найдёт обычного равенства и пройдёт NOT EXISTS. Следовательно, это не механическая замена одного оператора другим.

Если NULL нужно считать равным NULL, сформулируйте это отдельно, например через IS NOT DISTINCT FROM в условии сопоставления. Такое решение меняет смысл сравнения, поэтому его следует подтвердить примером.

Как проверить реальный запрос

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

Запросы выше используют только CTE с VALUES и предназначены для отдельного учебного подключения. Они не создают и не изменяют постоянные таблицы. Логику фильтров и реальные планы можно разобрать с наставником по PostgreSQL.