NOT IN, NOT EXISTS et NULL: éviter les erreurs SQL Server

Un rapport recherche les clients qui n'ont jamais commandé. Il fonctionne pendant des mois puis ne renvoie soudain plus rien. Pourtant, de nombreux clients restent inactifs et le SQL n'a pas changé. Une seule commande importée avec une référence client manquante peut déclencher le problème si le rapport utilise NOT IN.

Les comparaisons avec NULL produisent une troisième valeur logique: inconnu. WHERE ne conserve que les lignes dont la condition est vraie. Inconnu ne passe donc pas le filtre. Cette règle explique le comportement plus précisément qu'une recommandation générale de remplacer tous les IN.

Reproduire le résultat

La première requête ne renvoie aucun client. NOT EXISTS renvoie 2 et 3. La jointure externe renvoie également 2 et 3, car la colonne testée ne peut pas être NULL sur une véritable commande. Le dernier résultat compte trois commandes, mais seulement deux références client renseignées.

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;

Pour le client 2, NOT IN exige que chaque comparaison d'inégalité avec les valeurs de la sous-requête réussisse. La comparaison à 1 est vraie; celle à NULL reste inconnue. La condition complète n'est pas vraie. Le client 1 est éliminé par sa correspondance réelle, et l'ensemble final est vide.

Exclure les NULL de la sous-requête peut corriger NOT IN pour cette clé externe non nullable. C'est acceptable si le contrat de nullabilité est explicite. Si une modification rend l'expression externe nullable, la décision doit être réexaminée. NOT EXISTS exprime plus directement le besoin d'absence de correspondance.

Définir la bonne relation

NOT EXISTS cherche n'importe quelle ligne correspondante. Les commandes dupliquées du client 1 ne multiplient pas le client 2. En revanche, cette construction ne rend pas NULL égal à NULL. Si deux valeurs manquantes doivent constituer une correspondance métier, l'égalité ordinaire dans la sous-requête ne met pas en oeuvre cette règle.

La jointure externe exige de choisir une colonne droite qui prouve l'absence de ligne. Une date de livraison nullable peut être vide sur une commande existante. OrderId est déclaré NOT NULL dans l'exemple; sa valeur NULL après jointure indique donc bien l'absence de correspondance.

Le placement des autres conditions change aussi le sens. Pour trouver les clients sans commande payée, placez le critère de paiement dans NOT EXISTS ou dans ON de la jointure externe. Un filtre sur l'état droit dans le WHERE externe peut supprimer les lignes étendues avec NULL que vous cherchez. Testez commandes payées, impayées et absentes avant toute optimisation.

Vérifier la sémantique avant le plan

Un index commençant par Orders.CustomerId peut rendre le test d'existence efficace sur une grande table. Avec un critère de paiement, examinez distribution et intérêt d'un index filtré ou composé. Plusieurs écritures SQL peuvent aboutir à des opérateurs d'anti-semi-jointure similaires. La forme textuelle seule ne désigne pas la plus rapide.

EXCEPT possède une sémantique d'ensemble: il élimine les doublons et considère les NULL comme égaux pour la comparaison distincte. Il n'est donc pas interchangeable lorsque multiplicité et valeurs manquantes font partie du contrat. Ajouter DISTINCT à une jointure accidentellement multiplicative peut aussi cacher une erreur et ajouter du travail.

Le jeu de tests doit inclure un côté droit vide, une correspondance, des doublons, un NULL à droite et une clé gauche nullable si le schéma le permet. Vérifiez les lignes attendues, pas seulement leur nombre. Deux résultats faux peuvent compter autant de lignes.

Enfin, recherchez pourquoi la référence manquante a pu entrer dans la base. Corriger le rapport ne complète pas les données amont. Une anti-jointure fiable rend explicite la règle d'absence, préserve le sens des NULL et n'utilise l'indexation qu'après validation de ces décisions.

Références techniques: Microsoft Learn: IN · Microsoft Learn: EXISTS.