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

NOT IN может вернуть неизвестный результат из-за NULL во множестве подзапроса, поэтому условие в WHERE может не выбрать ни одной строки. NOT EXISTS проверяет наличие подходящей строки и не становится неизвестным только из-за NULL в других строках подзапроса; при потенциально nullable-значениях он обычно безопаснее.

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

NOT IN возник как предикат проверки отсутствия значения в множестве, а NOT EXISTS — как проверка отсутствия связанных строк. Их семантика опирается на разные модели: NOT IN сравнивает значение с элементами множества, а NOT EXISTS оценивает сам факт существования результата подзапроса.

В SQL присутствует трёхзначная логика: результат сравнения может быть TRUE, FALSE или UNKNOWN. Значение NULL не равно ни одному значению, включая другой NULL, поэтому обычные сравнения с ним дают UNKNOWN.

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

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

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

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

NOT IN логически связан с проверкой, что внешнее значение неравно каждому значению из результата подзапроса. Если среди этих значений есть NULL, сравнение с ним неизвестно. Когда ни одно сравнение не дало TRUE, итоговое отрицание также может остаться UNKNOWN.

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

WITH customers(id) AS (
    VALUES (1), (2), (3)
), orders(customer_id) AS (
    VALUES (1), (NULL)
)
SELECT c.id
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o);

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

Первый запрос не вернёт клиентов 2 и 3: наличие NULL делает результат проверки неизвестным. Второй запрос вернёт 2 и 3, потому что для них не существует заказа с равным идентификатором.

NOT IN допустим, если гарантировано, что выражение подзапроса не может быть NULL. Это можно обеспечить ограничением NOT NULL или явным исключением NULL из подзапроса. Однако при сложной схеме и изменяющихся ограничениях NOT EXISTS обычно лучше выражает требуемую анти-связь.

Оптимизатор может преобразовать оба варианта в операцию антисоединения, но только с сохранением их различий в обработке NULL. Поэтому нельзя судить об эквивалентности только по ожидаемому плану или производительности; сначала нужно проверить семантику данных.

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

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

Рассматривались три решения. Сохранить NOT IN без изменений было проще всего, но оставляло ошибку. Добавить фильтр IS NOT NULL в подзапрос можно было при подтверждённой бизнес-семантике, однако это требовало не забыть о фильтре при будущих изменениях. Переписать условие на NOT EXISTS было наиболее явно и не зависело от наличия посторонних NULL в таблице.

Выбрали NOT EXISTS, а для столбца дополнительно добавили проверку качества данных. Отчёт начал возвращать клиентов 2 и 3, а некорректные записи стали отдельно видны контролю качества. Производительность проверили по плану и индексу на ключе связи, поскольку выбор формы предиката не гарантирует одинаковый план в каждой СУБД.

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

Что произойдёт, если подзапрос для NOT IN вернёт пустой набор?

Для ненулевого внешнего значения NOT IN вернёт TRUE, потому что значение не принадлежит пустому множеству. NOT EXISTS также вернёт TRUE, поскольку подходящих строк не существует.

Но если само внешнее значение равно NULL, NOT IN даст UNKNOWN, даже при пустом подзапросе. NOT EXISTS вернёт TRUE, если коррелированное условие не нашло строк, потому что он проверяет отсутствие результата, а не сравнивает NULL с элементами множества.
Можно ли считать NOT IN и NOT EXISTS одинаковыми после оптимизации?

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

Поэтому совпадение планов выполнения не доказывает логическую эквивалентность запросов. Эквивалентность нужно обосновывать ограничениями на данные и условиями предиката.
Как безопасно оставить NOT IN, если внутренний столбец допускает NULL?

Нужно явно исключить NULL из результата подзапроса, если такие значения не должны участвовать в проверке отсутствия. Например, внутреннее условие может отбирать только ненулевые идентификаторы.

Более устойчивый вариант — использовать NOT EXISTS с коррелированным сравнением. Если бизнес-правило считает два отсутствующих значения совпадающими, обычного равенства недостаточно: это правило нужно выразить отдельным null-безопасным условием, а не надеяться на поведение NOT IN.