Valeurs NULL dans la clause NOT IN

Question

Ce problème est apparu lorsque j'ai obtenu des nombres d'enregistrements différents pour des requêtes que je pensais être identiques, l'une utilisant un fichier de type not in where et l'autre une contrainte left join . Le tableau de la not in avait une valeur nulle (mauvaise donnée), ce qui a fait que la requête a retourné un compte de 0 enregistrement. Je comprends plus ou moins pourquoi, mais j'aurais besoin d'aide pour bien comprendre le concept.

En d'autres termes, pourquoi la requête A renvoie-t-elle un résultat alors que la requête B n'en renvoie pas ?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

C'était sur SQL Server 2005. J'ai également constaté qu'appeler set ansi_nulls off fait que B renvoie un résultat.

Answer 1

La requête A est la même que :

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Desde 3 = 3 est vrai, vous obtenez un résultat.

La requête B est la même que :

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

Lorsque ansi_nulls est en cours, 3 <> null est UNKNOWN, donc le prédicat est évalué à UNKNOWN, et vous n'obtenez pas de lignes.

Lorsque ansi_nulls est éteint, 3 <> null est vrai, donc le prédicat est évalué à vrai, et vous obtenez une ligne.

Answer 2

Chaque fois que vous utilisez NULL, vous avez affaire à une logique à trois valeurs.

Votre première requête renvoie des résultats car la clause WHERE évalue à :

    3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
    FALSE or FALSE or TRUE or UNKNOWN
which evaluates to 
    TRUE

Le deuxième :

    3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
    TRUE and TRUE and UNKNOWN
which evaluates to:
    UNKNOWN

Le UNKNOWN n'est pas la même chose que le FALSE. vous pouvez facilement le tester en appelant

select 'true' where 3 <> null
select 'true' where not (3 <> null)

Les deux requêtes ne donneront aucun résultat

Si UNKNOWN était identique à FALSE, en supposant que la première requête donne FALSE, la seconde devrait donner VRAI car elle aurait été identique à NOT(FALSE).
Ce n'est pas le cas.

Il y a un très bon article sur ce sujet sur SqlServerCentral .

La question des NULL et de la logique à trois valeurs peut être un peu déroutante au début, mais il est essentiel de la comprendre afin d'écrire des requêtes correctes en TSQL.

Un autre article que je recommande est Fonctions agrégées SQL et NULL .

Answer 3

Comparer à null n'est pas défini, sauf si vous utilisez IS NULL.

Ainsi, lorsque l'on compare 3 à NULL (requête A), on obtient un résultat indéfini.

Par exemple, SELECT 'true' où 3 est dans (1,2,null) et SELECT 'true' où 3 n'est pas dans (1,2,null)

produira le même résultat, car NOT (UNDEFINED) est toujours indéfini, mais pas VRAI.

Answer 4

Dans A, l'égalité de 3 est testée par rapport à chaque membre de l'ensemble, ce qui donne (FAUX, FAUX, VRAI, INCONNU). Puisque l'un des éléments est VRAI, la condition est VRAIE (il est également possible qu'un court-circuit ait lieu ici, de sorte qu'il s'arrête en fait dès qu'il atteint le premier VRAI et n'évalue jamais 3=NULL).

En B, je pense qu'il évalue la condition comme NOT (3 dans (1,2,null)). Le test d'égalité de 3 par rapport à l'ensemble donne (FALSE, FALSE, UNKNOWN), qui est agrégé en UNKNOWN. NOT ( UNKNOWN ) donne UNKNOWN. Donc, dans l'ensemble, la vérité de la condition est inconnue, ce qui, à la fin, est essentiellement traité comme FALSE.

Answer 5

Null signifie l'absence de données, c'est-à-dire qu'il s'agit d'une donnée inconnue, et non d'une valeur de rien. Il est très facile pour les personnes issues du monde de la programmation de confondre cela, car dans les langages de type C, lorsque l'on utilise des pointeurs, null n'est en fait rien.

Par conséquent, dans le premier cas, 3 est bien dans l'ensemble (1,2,3,null), donc le résultat est vrai.

Dans le second cas, vous pouvez le réduire à

select 'true' where 3 not in (null)

Donc rien n'est renvoyé parce que l'analyseur ne sait rien de l'ensemble auquel vous le comparez - ce n'est pas un ensemble vide mais un ensemble inconnu. Utiliser (1, 2, null) n'aide pas parce que l'ensemble (1,2) est évidemment faux, mais alors vous le comparez à unknown, qui est inconnu.