août
2012
Un article de zinzineti
Soit les tables T1 et T2 suivantes :
create table T2 (col2 int)
insert into T1 select 1 union select 2 union select 3 union select null
insert into T2 select 4 union select 2 union select 5 union select null
Pourquoi la requête SELECT * FROM T1 WHERE col1 NOT IN (SELECT col2 FROM T2) ne renvoie aucune valeur alors que
SELECT * FROM T1 WHERE col1 IN (SELECT col2 FROM T2) renvoie la valeur 2 ?
Rappelons qu’une colonne affiche NULL lorsqu’elle ne contient pas de valeur. En SQL une opération de comparaison peut être avoir l’une des trois valeurs logiques suivantes : TRUE (vrai) ou FALSE (faux) ou UNKNOWN (inconnu). C’est ce qu’on a appelle « Three-Valued Logic » (3VL). Signalons qu’en mathématiques, l’algèbre de Boole est 2VL et ne connait donc que deux valeurs (VRAI ou FAUX). En SQL la valeur logique UNKNOWN est due à la présence de NULL dans une opération de comparaison.
Exemple :
——————————————————
p condition état logique
——————————————————
2 p IS NULL FALSE
——————————————————
2 p IS NOT NULL TRUE
——————————————————
NULL p IS NULL TRUE
——————————————————
NULL p IS NOT NULL FALSE
——————————————————
2 p = NULL UNKNOWN
——————————————————
2 p <> NULL UNKNOWN
——————————————————
NULL p = NULL UNKNOWN (pour SQL standard)
——————————————————
NULL p <> NULL UNKNOWN
——————————————————
NULL p = 2 UNKNOWN
——————————————————
NULL p <> 2 UNKNOWN
——————————————————
avec le tableau ci-dessus on peut constater que si l’un des opérandes est NULL alors l’opération de comparaison prend la valeur logique UNKNOWN. Pour déterminer si une expression est NULL (ou pas) il faut utiliser IS NULL (ou IS NOT NULL)
=> Algèbre Three-Valued Logic (3VL) et les opérateurs NOT, AND et OR
–> avec l’opérateur NOT
NOT TRUE = FALSE
NOT FALSE = TRUE
NOT UNKNOWN = UNKNOWN
–> avec l’opérateur AND
TRUE AND TRUE = TRUE
TRUE AND FALSE = FALSE
TRUE AND UNKNOWN = UNKNOWN
FALSE AND TRUE = FALSE
FALSE AND FALSE = FALSE
FALSE AND UNKNOWN = FALSE
UNKNOWN AND TRUE = UNKNOWN
UNKNOWN AND FALSE = FALSE
UNKNOWN AND UNKNOWN = UNKNOWN
–> avec l’opérateur OR
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
TRUE OR UNKNOWN = TRUE
FALSE OR TRUE = TRUE
FALSE OR FALSE = FALSE
FALSE OR UNKNOWN = UNKNOWN
UNKNOWN OR TRUE = TRUE
UNKNOWN OR FALSE = UNKNOWN
UNKNOWN OR UNKNOWN = UNKNOWN
=> UNKNOWN avec les clauses WHERE et CHECK
Dans une clause WHERE une expression évaluée comme UNKNOWN se comporte comme FALSE et dans ce cas aucune ligne n’est renvoyée.
Exemple :
select * from T1 where col1 not in (select col2 from T2)
Par contre dans une clause CHECK une expression évaluée comme UNKNOWN se comporte comme TRUE et l’expression est considérée comme valide
Exemple
insert into T2 select 4 union select 2 union select null
— insertion réalisée
=> opération arithmétique avec NULL renvoie NULL et peut donc provoquer un état logique UNKNOWN lors d’une comparaison
SELECT 3 - null
SELECT 3 * null
SELECT 3 /null
=> La plupart des fonctions d’agregation ignorent NULL
select COUNT(*) from T2 -- donne 4 : ici null n'est pas ignoré
select COUNT(col2) from T2 -- donne 3 : ici null est ignoré
=> Les opérateurs ensemblistes UNION,INTERSECT,EXCEPT n’ignorent pas NULL
select * from T1 EXCEPT select * from T2
select * from T1 INTERSECT select * from T2
=> CASE expression : attention lorsque la clause ELSE n’est pas explicite ELSE NULL est rajouté !
Exemple
when 1 then 1
when 2 then 2
when 3 then 3
END
FROM T1
Les fonctions CASE, COALESCE, … permettent de traiter les NULLs afin d’obtenir les résultats escomptés.
——————–
Etienne ZINZINDOHOUE
——————–