NOT IN, NOT EXISTS и NULL: ошибки anti-join в SQL Server

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

В SQL сравнение с NULL может дать неизвестное значение наряду с истинным и ложным. WHERE оставляет только строки, для которых условие истинно. Неизвестное значение фильтр не проходит. Понимание этой логики полезнее, чем безусловный совет заменять каждую конструкцию IN.

Воспроизвести неожиданное поведение

Первая выборка не возвращает клиентов. Вариант NOT EXISTS возвращает 2 и 3. Left join также возвращает 2 и 3, поскольку проверяемый столбец не может быть NULL в настоящем заказе. Последняя выборка считает три заказа, но лишь две заполненные клиентские ссылки.

DECLARE @Customers TABLE(CustomerId int NOT NULL PRIMARY KEY);
DECLARE @Orders TABLE(OrderId int NOT NULL, CustomerId int NULL);
INSERT @Customers VALUES (1),(2),(3);
INSERT @Orders VALUES (10,1),(11,NULL),(12,1);

SELECT c.CustomerId
FROM @Customers AS c
WHERE c.CustomerId NOT IN
    (SELECT o.CustomerId FROM @Orders AS o);

SELECT c.CustomerId
FROM @Customers AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM @Orders AS o
    WHERE o.CustomerId=c.CustomerId
);

SELECT c.CustomerId
FROM @Customers AS c
LEFT JOIN @Orders AS o ON o.CustomerId=c.CustomerId
WHERE o.OrderId IS NULL;

SELECT COUNT(*) AS AllOrders,
       COUNT(CustomerId) AS OrdersWithCustomer
FROM @Orders;

Для клиента 2 условие NOT IN требует, чтобы неравенство выполнялось для каждого значения подзапроса. Сравнение с 1 истинно, но сравнение с NULL неизвестно. Итоговое условие не становится истинным. Клиент 1 исключается из-за реального совпадения, поэтому не остается ни одной строки.

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

Выбрать именно нужную связь

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

Для left join нужно проверять правый столбец, однозначно указывающий на отсутствие записи. Допускающая NULL дата доставки может быть пустой и в реальном заказе. В примере OrderId объявлен NOT NULL, поэтому NULL после соединения действительно означает отсутствие совпадения.

Положение дополнительных условий меняет смысл. Для клиентов без оплаченных заказов условие оплаты следует поместить внутрь NOT EXISTS или в ON соответствующего left join. Фильтрация правого статуса во внешнем WHERE может удалить именно дополненные NULL строки, которые требовалось найти. Проверьте оплаченные, неоплаченные и отсутствующие заказы на маленьком наборе.

Для разных отчетов отсутствие имеет разный смысл. «Ни одного заказа за последний месяц» не равно «ни одного заказа вообще». Временная граница должна участвовать в определении подходящей правой записи. Добавление ее к внешнему запросу после соединения легко меняет бизнес-правило, особенно если часть клиентов имеет только старые заказы. Поэтому ожидаемые ответы лучше записать до изменения SQL.

Проверить корректность до оптимизации

На большой таблице индекс, начинающийся с Orders.CustomerId, может поддержать проверку существования. Если участвует статус оплаты, учитывайте распределение данных и возможность составного либо фильтрованного индекса. Разные синтаксические формы могут давать похожие операторы anti-semi join. По тексту запроса нельзя надежно выбрать самый быстрый вариант.

EXCEPT использует семантику множеств, удаляет дубликаты и считает NULL равными при сравнении различных значений. Поэтому это не прозрачная замена, когда кратность или правила пустых ключей важны. DISTINCT после случайного соединения многие-ко-многим также способен скрыть ошибку модели и добавить лишнюю работу.

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

Если проблема возникла после импорта, выясните также, почему отсутствующая ссылка была принята. Исправление отчета не восполняет данные источника. Надежный anti-join явно определяет отсутствие связи, сохраняет требуемую семантику NULL и лишь затем оптимизируется индексами.

Техническая документация: Microsoft Learn: IN · Microsoft Learn: EXISTS.