Être ou ne pas être NULL…telle est la question !

Certaines personnes préfèrent ne jamais avoir de colonnes marquées à NULL dans leurs bases de données. Cette position intégriste est une stupidité. Par exemple si vous notez la date de naissance des personnes, alors quelle valeur donnerez-vous à cette colonne pour indiquer que vous ne savez pas ? Facile, me direz-vous, on conviendra d'une valeur arbitraire, par exemple le 1er janvier 1900… Conséquence, ce que vous aviez pris pour une bonne idée devient vite un cauchemar… Car dès lors, la plupart des requêtes vont devenir un enfer et les performances se dégrader. Comment calculer l'âge par exemple ?

Il en est de même de l'usage de la chaîne vide ou du zéro pour « pallier » les NULL. Vous aurez l'air malin quand vous voudrez connaître le prix moyen d'un produit, si vous l'avez quantifié à zéro dans une ligne de facture : diviser par zéro vous assurera une erreur que le NULL vous aurait évitée !

Chez les adeptes du NULL, l'idée émergente a longtemps été de rajouter quelques marqueurs supplémentaires : valeur inapplicable, valeur inconnue, etc.

Dans un document devenu célèbre (Interim Report 75-02-08 voir en ref. bibliographique), les logiciens préconisaient l'usage de 14 marqueurs différents afin de rendre compte des situations d'incomplétude de données. Le docteur Codd classifia cela en deux grandes familles : les valeurs inapplicables (par exemple la couleur du toit d'une moto) et les valeurs inconnues (par exemple la couleur des yeux d'une personne portant des lunettes de soleil).

Mais la chose ne manquait pas d'intérêt ! Comment par exemple faire en sorte de stocker une date représentant TOUJOURS le futur ?
Un petit exemple : vous avez une table de tarifs applicable avec des dates butoirs. Les dates butoirs sont DATE_DEBUT et DATE_FIN. Comment spécifier que le tarif le plus récent doit avoir pour la colonne DATE_FIN, une valeur toujours supérieure à la date du jour ?
Autrement dit, comment dans une base de données, représenter l'infini positif ou l'infini négatif ?
De même comment représenter le débordement ?

SELECT CAST(32767 + 1 AS SMALLINT)
FROM ...

Ou bien même, comment représenter une erreur, afin de la traiter « en ligne » dans la requête ?
Ce serait tellement plus simple si l'on pouvait faire, par exemple :

SELECT CASE
          WHEN PRIX / QUANTITE IS ERROR
             THEN '***'
          ELSE CAST(PRIX / QUANTITE AS VARCHAR(32)) + ' €'
       END AS PRIX_MOYEN
FROM ...

Mais la logique de traitement des marqueurs s'avéra à ce point complexe et rebutante qu'elle ne fût jamais réellement inscrite dans la norme et que les éditeurs refusèrent de dépenser le moindre centime en implémentation de cette extension…

Il s'agit simplement de savoir si une colonne ou une expression est marquée à NULL

Exemple :

SELECT ...
FROM   ...
WHERE  COLONNE1 IS NULL OR COLONNE2 IS NULL

SELECT ...
FROM   ...
WHERE  (COLONNE1 + COLONNE2) IS NULL

Il est possible de remplacer à la volée, donc dans une requête, un marqueur NULL par une valeur conventionnelle. Par exemple, zéro, chaîne vide, etc. La particularité de cette fonction est d'accepter autant de paramètres que l'on veut (sans limitation théorique).

Sa syntaxe est la suivante :

COALESCE(expression1 [, expression2 [, expression3 [ ... ] ] ])

Elle renvoie la première expression évaluable (donc ne contenant pas de marqueur NULL) dans l'ordre positionnel de l'écriture de gauche à droite.

Exemple :

SELECT COALESCE(debit, credit * -1, 0)
FROM   ...

Dans cet exemple, l'évaluation ligne à ligne renverra la valeur de la colonne debit si elle n'est pas marquée à NULL, sinon, la valeur de l'expression « credit * -1 » si credit n'est pas marqué à NULL sinon, dans le cas ou debit et credit sont marqués à NULL, la valeur zéro.

NULLIF permet de marquer à NULL une expression donnant une certaine valeur.

Exemple :

SELECT NULLIF(debit, credit)
FROM   ...

Renverra le marqueur NULL à chaque fois que debit et credit ont la même valeur. Donc ne sont pas marqués à NULL, ni l'un ni l'autre.

On peut aussi utiliser les structures CASE et l'UNION pour traiter les NULL :

SELECT CASE
          WHEN debit IS NULL AND credit IS NULL
             THEN 0
          WHEN debit IS NULL AND credit IS NOT NULL
             THEN debit * -1
          WHEN debit IS NOT NULL AND credit IS NULL
             THEN credit
          WHEN debit IS NOT NULL AND credit IS NOT NULL
             THEN credit - debit
       END
FROM ...

SELECT 0
FROM   ...
WHERE  debit IS NULL AND credit IS NULL
UNION
SELECT debit * -1
FROM   ...
WHERE  debit IS NULL AND credit IS NOT NULL
UNION
SELECT credit
FROM   ...
WHERE  debit IS NOT NULL AND credit IS NULL
UNION
SELECT credit - debit
WHERE  debit IS NOT NULL AND credit IS NOT NULL

Le mot clef DECLARE qui permet de spécifier une variable dans un code procédural SQL (procédure stockée, fonction, triggers…) marque immédiatement la variable déclarée à NULL.

DECLARE toto INTEGER

SELECT I11 + toto AS I
FROM   T1

I
----------------
NULL
NULL
NULL
NULL

Il convient donc de l'initialiser si l'on veut l'utiliser dans une expression.

Par exception à la règle de propagation des NULL, l'absence de valeur dans une colonne n'a pas pour effet d'entacher le calcul d'un agrégat.
Si l'on utilise SUM, AVG, MAX ou MIN, le résultat renverra toujours une valeur si au moins une valeur existe, sinon le marqueur NULL.
En revanche, COUNT renverra toujours une valeur et jamais le marqueur NULL : lorsque toutes les occurrences de la colonne sont NULL, COUNT renvoie zéro.

SELECT AVG(I22)   AS AVG,
       SUM(I22)   AS SUM,
       COUNT(I22) AS COUNT,
       MAX(I22)   AS MAX,
       MIN(I22)   AS MIN

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

AVG         SUM         COUNT       MAX         MIN        
----------- ----------- ----------- ----------- -----------
1           9           6           2           1

SELECT AVG(I22) AS AVG,
       SUM(I22) AS SUM,
       MAX(I22) AS MAX,
       MIN(I22) AS MIN

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

WHERE  I22 < 1

AVG         SUM         MAX         MIN        
----------- ----------- ----------- -----------
NULL        NULL        NULL        NULL

SELECT COUNT(I22) AS COUNT

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

WHERE  I22 < 1

COUNT      
-----------
0

Attention, car vos statistiques peuvent être faussées… Par exemple le calcul d'une moyenne est différent si l'on dénullifie à 0 ou 1…

Exemple :

SELECT AVG(COALESCE(I22, 0))   AS AVG,
       SUM(COALESCE(I22, 0))   AS SUM,
       COUNT(COALESCE(I22, 0)) AS COUNT,
       MAX(COALESCE(I22, 0))   AS MAX,
       MIN(COALESCE(I22, 0))   AS MIN

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

AVG         SUM         COUNT       MAX         MIN         
----------- ----------- ----------- ----------- -----------
0           9           12          2           0

AVG et MIN n'ont plus les mêmes valeurs…

COUNT(*) est un calcul d'agrégat un peu particulier dans le sens où il compte le nombre de lignes que possède la table. Dans ce cas, même si la ligne voit toutes ses colonnes marquées à NULL, elle est quand même comptée. C'est l'exception de l'exception !

SELECT COUNT(*) AS COUNT

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

COUNT       
-----------
12

SELECT *

FROM   T1
       FULL OUTER JOIN T2
            ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22

I11         I12         I21         I22        
----------- ----------- ----------- -----------
NULL        NULL        NULL        NULL
NULL        NULL        1           NULL
NULL        NULL        NULL        1
1           1           1           1
NULL        NULL        1           2
NULL        NULL        2           1
NULL        NULL        2           2
NULL        NULL        2           NULL
NULL        NULL        NULL        2
NULL        NULL        NULL        NULL
NULL        1           NULL        NULL
1           NULL        NULL        NULL

Les 1re et 10e lignes entièrement à NULL ont bien été comptées !

Le prédicat EXISTS est évalué à vrai, même si les lignes retournées ont toutes leurs colonnes marquées à NULL :

CREATE TABLE T_NULL
(I1 INTEGER,
 I2 INTEGER)

INSERT INTO T_NULL
VALUES (NULL, NULL)

SELECT *
FROM   T_NULL

I1          I2         
----------- -----------
NULL        NULL

SELECT 'OUI' AS NULL_EXISTE
FROM   T_NULL
WHERE  EXISTS(SELECT *
              FROM T_NULL)

NULL_EXISTE
-----------
OUI

En dépit du fait que la seule ligne insérée dans la table est « vide », EXISTS indique qu'il y a bien une ligne…

On rencontrera un comportement similaire avec les prédicats MATCH et UNIQUE (attention pour le UNIQUE, si plusieurs lignes renvoyées sont NULL, le principe est que l'unicité est conservée !)…

Une question intéressante est de savoir dans quel ordre doivent se trouver les marqueurs NULL ?
Comme il ne s'agit pas de valeurs, une clause telle que ORDER BY peut difficilement décider de les trier…
Mais la norme SQL:1999 (Window partionning, OLAP) a bien fait les choses, puisqu'elle précise un paramètre d'ordonnancement des NULL qui peut prendre la valeur NULL FIRST ou NULL LAST.

Le problème c'est que peu d'éditeurs ont opté pour un tel raffinement. Ainsi dans la documentation de MS SQL Server, on trouve la mention :
« Les valeurs NULL sont traitées comme les valeurs les plus petites possible. »

Tant et si bien que la même requête basique avec un ORDER BY ne donnera pas le même classement, DB2 UDB ayant opté pour l'inverse ! En effet , dans son aide en ligne IBM indique que « Ordering is performed in accordance with the comparison rules described in »Language Elements« . The null value is higher than all other values. »

Aussi bizarre que cela puisse paraître, l'utilisation de DISTINCT dans la clause SELECT de l'ordre SELECT ne doit pas « dédoublonner » les lignes à NULL. Pourquoi ? Parce qu’encore une fois, toute comparaison de NULL est impossible, donc chaque NULL est différent !
C'est assez logique d'ailleurs. Par exemple, si vous avez une colonne représentant le n° de téléphone de vos proches et que cette colonne n'est pas toujours renseignée, il parait évident que les n° que vous mettrez à la place des marqueurs vides seront différents les uns des autres (ou alors c'est que vous n'avez pas tenu compte des principes fondamentaux de la normalisation !)

Exemple :

CREATE TABLE T_SELECT_DISTINCT
(COL1 INTEGER,
 COL2 INTEGER)

INSERT INTO T_SELECT_DISTINCT VALUES (NULL, NULL)
INSERT INTO T_SELECT_DISTINCT VALUES (NULL, NULL)
INSERT INTO T_SELECT_DISTINCT VALUES (NULL, 4)
INSERT INTO T_SELECT_DISTINCT VALUES (1, 1)
INSERT INTO T_SELECT_DISTINCT VALUES (1, 1)
INSERT INTO T_SELECT_DISTINCT VALUES (0, 2)

SELECT DISTINCT *
FROM T_SELECT_DISTINCT

COL1        COL2        
----------- -----------
NULL        NULL
NULL        NULL
NULL        4
0           2
1           1

SELECT ALL *
FROM T_SELECT_DISTINCT

COL1        COL2       
----------- -----------
NULL        NULL
NULL        NULL
1           1
1           1
0           2
NULL        4

Mais à ma connaissance, aucun SGBDR ne traite le dédoublonnement des NULL de cette façon, car tous considèrent que NULL est une « valeur » !

Il en est de même dans le cas de l'union… Par défaut l'UNION de deux résultats de requête est dédoublonné. Sauf pour les lignes dont les colonnes sont marquées à NULL…

CREATE TABLE T_SELECT_DISTINCT_UNION
(COL1 INTEGER,
 COL2 INTEGER)

INSERT INTO T_SELECT_DISTINCT_UNION VALUES (NULL, NULL)
INSERT INTO T_SELECT_DISTINCT_UNION VALUES (NULL, 4)
INSERT INTO T_SELECT_DISTINCT_UNION VALUES (4, NULL)
INSERT INTO T_SELECT_DISTINCT_UNION VALUES (1, 1)
INSERT INTO T_SELECT_DISTINCT_UNION VALUES (0, 2)

SELECT *
FROM   T_SELECT_DISTINCT
UNION
SELECT *
FROM   T_SELECT_DISTINCT_UNION

COL1        COL2       
----------- -----------
NULL        NULL
NULL        NULL
NULL        NULL
NULL        4
NULL        4
0           2
1           1
4           NULL

SELECT *
FROM   T_SELECT_DISTINCT
UNION ALL
SELECT *
FROM   T_SELECT_DISTINCT_UNION

COL1        COL2       
----------- -----------
NULL        NULL
NULL        NULL
NULL        NULL
NULL        4
NULL        4
0           2
0           2
1           1
1           1
1           1
4           NULL

Même constat chez les éditeurs de SGBDR ! Aucun éditeur n'a pensé à conserver les doublons de NULL dans l'UNION « distinct »…

En l'absence de toute spécification dans l'ordre de création, une colonne peut ne contenir aucune valeur.

En l'occurrence, les deux formulations :

CREATE TABLE T_ANOTHER_NULL
(COL1 INTEGER,
 COL2 VARCHAR(16))

CREATE TABLE T_ANOTHER_NULL
(COL1 INTEGER     NULL,
 COL2 VARCHAR(16) NULL)

sont équivalentes… car par défaut, la contrainte est : marqueur NULL possible.

Si l'on veut contraindre la colonne à toujours devoir spécifier une valeur, il faut ajouter la contrainte NOT NULL.

Dans une contrainte d'unicité, si l'on ne précise pas NOT NULL comme contrainte des colonnes visées par la contrainte « UNIQUE » alors il pourra y avoir autant de tuples contraints n'ayant pas de valeur spécifique.

Exemple :

CREATE TABLE T_UNIQUE_NULL
(COL1 INTEGER,
 COL2 INTEGER,
 CONSTRAINT UNICOL UNIQUE (COL1, COL2))

INSERT INTO T_UNIQUE_NULL VALUES (1, 1)
INSERT INTO T_UNIQUE_NULL VALUES (1, NULL)
INSERT INTO T_UNIQUE_NULL VALUES (NULL, 1)
INSERT INTO T_UNIQUE_NULL VALUES (NULL, NULL)
INSERT INTO T_UNIQUE_NULL VALUES (NULL, NULL)
INSERT INTO T_UNIQUE_NULL VALUES (1, NULL)
INSERT INTO T_UNIQUE_NULL VALUES (1, 1)

La contrainte ne sera violée QUE pour la dernière ligne. La 5e provoquera l'insertion d'un doublon de ligne NULL sans déclencher de violation de contrainte d'unicité, car la comparaison entre NULL est par définition une hérésie !

La violation des contraintes d'intégrité en présence de marqueur NULL dépend de la clause MATCH qui permet d'indiquer de quelle façon traiter l'absence de valeur.
Ainsi, un « MATCH SIMPLE » implique que toutes les colonnes composant la contrainte d'intégrité soient renseignées. Dans le cas où l'une des colonnes possède un marqueur NULL, la contrainte est violée.
À lire sur le sujet :
Mais peu de SGBDR vont jusque là dans la gestion de l'intégrité référentielle.

On peut spécifier le marqueur NULL comme absence de valeur de colonnes lors d'une insertion

Exemple :

INSERT INTO T_CLIENT  (CLI_ID, TIT_CODE, CLI_NOM,    CLI_PRENOM, CLI_ENSEIGNE)
VALUES                (999,    'M.',     'DUMOULIN', 'Alfred',   NULL        )

INSERT INTO T_CLIENT
VALUES (999, 'M.', 'DUMOULIN', 'Alfred', NULL)

NOTA : ces deux requêtes sont équivalentes.

Point n'est besoin de spécifier le marqueur NULL si l'on ne spécifie pas la colonne dans la liste des colonnes cibles.

Exemple :

INSERT INTO T_CLIENT  (CLI_ID, TIT_CODE, CLI_NOM,    CLI_PRENOM)
VALUES                (999,    'M.',     'DUMOULIN', 'Alfred'  )

NOTA : cette requête est équivalente aux deux précédentes.

On peut spécifier une contrainte de défaut NULL et faire usage du mot clef DEFAULT lors de l'insertion.

Exemple :

CREATE TABLE T_DEFAUT_NULL
(COL1 INTEGER DEFAULT NULL,
 COL2 INTEGER)

INSERT INTO  T_DEFAUT_NULL VALUES (DEFAULT, DEFAULT)
INSERT INTO  T_DEFAUT_NULL VALUES (NULL, DEFAULT)
INSERT INTO  T_DEFAUT_NULL VALUES (DEFAULT, NULL)

SELECT * FROM
T_DEFAUT_NULL

COL1        COL2       
----------- -----------
NULL        NULL
NULL        NULL
NULL        NULL

On peut supprimer la valeur d'une colonne en assignant le marqueur NULL.

Exemple :

UPDATE T_CLIENT
SET CLI_ENSEIGNE = NULL

UPDATE T_CLIENT
SET CLI_ENSEIGNE = DEFAULT

Ces deux écritures conduiront toutes deux à effacer toutes les valeurs contenues dans la colonne CLI_ENSEIGNE de la table T_CLIENT, pourvu que la colonne CLI_ENSEIGNE soit « NULLABLE ».

NOTA : on peut remarquer que la norme à manqué de finesse en utilisant un « = NULL » mal à propos pour la désassignation de valeur, alors qu'elle nous oblige à utiliser IS NULL et non = NULL dans un prédicat. On aurait préféré rester cohérent et ajouter une désassignation telle que : SET NULL par exemple.

Certains SGBDR utilisent d'autres fonctions pour dénullifier. Par exemple on trouve NVL sous Oracle, en plus de COALESCE : cette fonction ne porte que sur deux arguments. On trouve également en alternative sous SQL Server une fonction ISNULL.

Sybase et SQL Server font à ce sujet, l'inverse de ce que spécifie la norme. Ainsi toute table créée sans précision de NULL sont NOT NULL !

Sybase n'accepte pas le CHAR sans contrainte NOT NULL. Il le transforme en VARCHAR. En fait il stocke le fait d'être NULL pour un VARCHAR en y mettant une chaine vide !
Plus curieux encore, si vous tentez de mettre une chaine vide dans une telle colonne, alors elle ne sera pas réellement vide. Elle sera composée d'un seul caractère blanc !

Peu de SGBDR acceptent la contrainte d'unicité telle que définie dans la norme. SQL Server la refuse et considère que différentes lignes à NULL violent la contrainte d'unicité. Il accepte une seule ligne avec des NULL, tandis que DB2 refuse tout NULL.

La plupart du temps, les anciennes syntaxes de traitement des jointures externes sont incohérentes. Lire la démonstration qu'a fait Steve Kass de la Drew University au sujet de SQL Server et des différents résultats qu'il obtient pour deux requêtes logiquement équivalentes, l'une basée sur la syntaxe des jointures normative et l'autre sur l'ancienne syntaxe en =*… Les jointures

Encore plus stupéfiant… Sous SQL Server, la requête :

SELECT *
FROM   T_CLIENT
WHERE  CLI_ENSEIGNE = NULL

ne provoque aucune erreur de syntaxe, et ne donne pas le même résultat que :

SELECT *
FROM   T_CLIENT
WHERE  CLI_ENSEIGNE IS NULL