MS SQL Server
  1. MS SQL Server
  3. SQL Server: Pourquoi la comparaison null = value returnne-t-elle true pour NOT IN?
Intereting Posts
Comment returnner une valeur d'une procédure stockée à EF Aide de search en text intégral Aucun outil de données SQL Server dans VS 2013 ultime installé Blocage SQLServer Impossible de charger le file ou l'assembly 'Microsoft.SqlServer.DTSRuntimeWrap' Notifier l'opérateur si l'une des étapes du travail échoue Ajouter plusieurs lignes dans une requête SQL SELECT Problèmes de réplication SQL Server Différence entre SQL_Latin1_General_Cp437_CI_AS_KI_WI et SQL_Latin1_General_Cp850_CI_AS_KI_WI Accès aux données dans ASP.NET MVC 3 Contrainte unique vs index unique Meilleures pratiques pour exécuter des scripts SQL Server à partir d'une application .NET? L'autorisation EXECUTE a été refusée sur l'object 'xxxxxxx', la database 'zzzzzzz', le schéma 'dbo' SQL Server: supprimer la key primaire de la table, sans connaître son nom Sélectionnez uniquement les mêmes valeurs que l'autre ligne

SQL Server: Pourquoi la comparaison null = value returnne-t-elle true pour NOT IN?

Pourquoi la comparaison de value à null renvoie-t-elle faux, sauf si vous utilisez un NOT IN , où elle renvoie true?

Étant donné une requête pour find tous les users de stackoverflow qui ont un post: 

 SELECT * FROM Users WHERE UserID IN (SELECT UserID FROM Posts)

Cela fonctionne comme prévu; J'ai une list de tous les users qui ont un post.

Maintenant, interrogez l'inverse. find tous les users de stackoverflow qui n'ont pas de post: 

 SELECT * FROM Users WHERE UserID NOT IN (SELECT UserID FROM Posts)

Cela ne renvoie aucun logging, ce qui est incorrect.

Compte tenu des données hypothétiques 1 

 Users Posts ================ =============================== UserID Username PostID UserID Subject ------ -------- ------- ------ ---------------- 1 atkins 1 1 Welcome to stack ov... 2 joels 2 2 Welcome all! ... ... ... ... 399573 gt6989b ... ... ... ... ... ... 10592 null (deleted by nsl&fbi... ... ...

Et assumez les règles de NULLs:

  • NULL = NULL évalue à inconnu
  • NULL <> NULL évalue à inconnu
  • value = NULL évalue inconnu

Si nous regardons la deuxième requête, nous sums intéressés à find toutes les lignes où le Users.UserID est introuvable dans la colonne Posts.UserID. Je procéderais logiquement comme suit:

Vérifiez UserID 1

  • 1 = 1 renvoie vrai. Nous concluons donc que cet user a des messages, et ne les inclut pas dans la list de sortie

Maintenant, vérifiez UserID 2:

  • 2 = 1 renvoie false, donc nous continuons à chercher
  • 2 = 2 renvoie vrai, donc nous concluons que cet user a des messages, et ne les inclut pas dans la list de sortie

Maintenant, vérifiez UserID 399573

  • 399573 = 1 renvoie false, donc nous continuons à chercher
  • 399573 = 2 renvoie false, donc nous continuons à chercher
  • …
  • 399573 = null renvoie inconnu, donc nous continuons à chercher
  • …

Nous n'avons pas trouvé de messages par UserID 399573, donc nous l'aurions inclus dans la list de sortie.

Sauf que SQL Server ne le fait pas. Si vous avez une valeur NULL dans votre list, alors elle trouve une correspondance. Il trouve soudainement un match. Soudainement 399573 = null évalue à vrai.

Pourquoi la comparaison de value à null renvoie-t-elle inconnue, sauf si elle returnne vrai?

Edit : je sais que je peux contourner ce comportement absurde en excluant spécifiquement les null: 

 SELECT * FROM Users WHERE UserID NOT IN ( SELECT UserID FROM Posts WHERE UserID IS NOT NULL)

Mais je ne devrais pas avoir à le faire, autant que je sache, la logique booleanne ne devrait pas poser de problème, d'où ma question.

Notes de bas de page

  • 1 données hypothétiques; si vous ne l'aimez pas: faites votre down.
  • Celko a maintenant son propre tag

Problème commun, réponse en conserve:

Le comportement de la clause NOT IN peut être déroutant et en tant que tel, il a besoin de quelques explications. Considérez la requête suivante: 

 SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)

Bien qu'il existe plus de mille noms de famille distincts dans AdventureWorks.Person.Contact, la requête ne renvoie rien. Cela peut sembler contre-intuitif pour un programmeur de database débutant, mais cela prend tout son sens. L'explication consiste en plusieurs étapes simples. Tout d'abord, considérons les deux requêtes suivantes, qui sont clairement équivalentes: 

 SELECT LastName, FirstName FROM Person.Contact WHERE LastName IN('Hedlund', 'Holloway', NULL) SELECT LastName, FirstName FROM Person.Contact WHERE LastName='Hedlund' OR LastName='Holloway' OR LastName=NULL

Notez que les deux requêtes renvoient les résultats attendus. Maintenant, callbackons le théorème de DeMorgan, qui stipule que: 

 not (P and Q) = (not P) or (not Q) not (P or Q) = (not P) and (not Q)

Je coupe et colle depuis Wikipedia (http://en.wikipedia.org/wiki/De_Morgan_duality). En appliquant le théorème de DeMorgan à ces requêtes, il s'ensuit que ces deux requêtes sont également équivalentes: 

 SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL) SELECT LastName, FirstName FROM Person.Contact WHERE LastName<>'Hedlund' AND LastName<>'Holloway' AND LastName<>NULL

Ce dernier nom <> NULL ne peut jamais être vrai

L'hypothèse dans votre première phrase n'est pas juste:

Pourquoi la comparaison de valeur à null renvoie-t-elle faux, sauf si vous utilisez un NOT IN, où elle renvoie true?

Mais la comparaison d'une valeur à null ne renvoie pas false ; il renvoie unknown . Et unknown a sa propre logique: 

 unknown AND true = unknown unknown OR true = true unknown OR false = unknown

Un exemple de comment cela fonctionne: 

 where 1 not in (2, null) --> where 1 <> 2 and 1 <> null --> where true and unknown --> where unknown

La clause where ne correspond qu'à true , donc cette option filter toutes les lignes.

Vous pouvez find la pleine gloire de la logique de 3 valeurs sur Wikipedia .