NOT IN, NOT EXISTS e NULL: erros de anti-join no SQL Server

Um relatório procura clientes que nunca fizeram pedidos. Funciona durante meses e de repente não retorna nada. Ainda existem clientes inativos e a consulta não mudou. Um único pedido importado sem referência de cliente pode revelar o problema quando o relatório utiliza NOT IN.

Comparações SQL com NULL produzem desconhecido, além de verdadeiro e falso. WHERE conserva somente as linhas cuja condição é verdadeira. Desconhecido não passa pelo filtro. Compreender isso é mais útil do que decorar uma recomendação genérica contra qualquer expressão IN.

Reproduzir o resultado inesperado

A primeira consulta não retorna clientes. NOT EXISTS retorna 2 e 3. O left join também retorna 2 e 3, pois testa uma coluna que não pode ser NULL em um pedido real. O último resultado conta três pedidos, mas apenas duas referências de cliente preenchidas.

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;

Para o cliente 2, NOT IN exige que as comparações de desigualdade com todos os valores da subconsulta sejam satisfeitas. Comparar com 1 resulta verdadeiro; comparar com NULL resulta desconhecido. A condição completa não é verdadeira. O cliente 1 é eliminado pela correspondência real, deixando o resultado vazio.

Remover NULL da subconsulta pode corrigir NOT IN para essa chave externa não anulável. É uma solução legítima quando a nulabilidade faz parte de um contrato documentado. Se a expressão externa passar a aceitar NULL, sua semântica precisa ser reavaliada. NOT EXISTS expressa mais diretamente a ausência de correspondência.

Definir a relação desejada

NOT EXISTS verifica se existe qualquer linha correspondente. Vários pedidos do cliente 1 não duplicam o cliente 2. Entretanto, isso não faz NULL ser igual a NULL. Se dois valores ausentes devem representar uma correspondência de negócio, a igualdade comum na subconsulta não implementa essa regra.

O left join precisa testar uma coluna direita que prove a ausência de uma linha. Uma data de entrega anulável pode estar vazia em um pedido existente. OrderId é declarado NOT NULL no exemplo; seu NULL depois da junção significa que não houve correspondência.

A posição dos filtros também muda o significado. Para localizar clientes sem pedidos pagos, coloque a condição de pagamento dentro de NOT EXISTS ou no ON do left join. Filtrar o estado direito no WHERE externo pode remover justamente as linhas estendidas com NULL que você procurava. Monte uma amostra com pedidos pagos, não pagos e inexistentes.

Verificar o significado antes do plano

Em uma tabela grande, um índice iniciado por Orders.CustomerId pode apoiar a verificação de existência. Se houver filtro de pagamento, considere distribuição e possibilidade de índice filtrado ou composto. Formas SQL diferentes podem produzir operadores anti-semi-join semelhantes. A sintaxe sozinha não determina a mais rápida.

EXCEPT tem semântica de conjunto, remove duplicações e trata NULL como igual na comparação distinta. Não é uma substituição transparente quando multiplicidade ou relacionamentos nulos importam. Adicionar DISTINCT depois de uma junção muitos-para-muitos acidental também pode esconder um erro e acrescentar processamento.

O conjunto de testes deve conter lado direito vazio, uma correspondência, correspondências duplicadas, NULL à direita e chave esquerda anulável quando permitida. Compare linhas esperadas, não apenas contagens. Dois resultados errados podem conter a mesma quantidade de registros.

Quando o defeito aparece após uma importação, investigue também por que a referência ausente foi aceita. Uma consulta corrigida não torna completos os dados de origem. Um anti-join confiável explicita a ausência relevante ao negócio, conserva a semântica dos NULL e só depois recebe otimizações de acesso.

Referências técnicas: Microsoft Learn: IN · Microsoft Learn: EXISTS.