Être ou ne pas être NULL...Telle est la question !

Date de publication : 17/09/2004

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

Sans vouloir être shakespearien la problématique du traitement des NULL rebute souvent les développeurs.
Le but de cet article est de démontrer son intérêt, les particularités, les chausses trappes et les moyens que la norme SQL nous donne pour traiter les NULL.


1. NULL c'est quoi ?
1.1. Pas de NULL svp !
1.2. Plus de "NULL" svp ?
2. Test des marqueurs NULL
2.1. Test direct : IS NULL, IS NOT NULL
2.2. Dénullification : COALESCE
2.3. Nullification : NULLIF
2.4. Structures de traitement des marqueurs NULL
3. Jointures externes
4. Traitement des marqueurs NULL
4.1. Déclaration de variable dans le code procédurale SQL : DECLARE
4.2. Les calculs d'agrégats statistiques ignorent les marqueurs NULL : COUNT, SUM, AVG, MAX ou MIN
4.3. Le comptage des lignes de la table prend en compte les lignes NULL ! : COUNT(*)
4.4. NULL existe bien !
4.5. L'ordre du NULL...
4.6. NULL avec DISTINCT et UNION
5. Définition de table et contraintes
5.1. Contrainte de colonne NUL / NOT NULL
5.2. Contrainte d'unicité
5.3. Autres contraintes
6. Mise à jour avec les NULL
6.1. NULL et INSERT
6.2. NULL et UPDATE
7. Particularité de certains SGBDR
7.1. Autres fonctions de traitement des NULL
7.2. NULL comme contrainte par défaut
7.3. NULL et CHAR
7.4. NULL et UNIQUE
7.5. Jointures externes et anciennes syntaxes
7.6. Prédicat = NULL !
8. Références bibliographiques


1. NULL c'est quoi ?

Première erreur courante, NULL, n'est pas une valeur. Pourtant on voit souvent fleurir dans la littérature comme dans les forums ou les pages Internet, des rédacteurs qui vous affirment que la colonne machin peut prendre la valeur NULL ! Or NULL n'est pas une valeur, c'est justement l'absence de valeur d'une colonne de table.
En fait NULL est un marqueur, tout comme l'est le nil de certains langages de programmation qui permettent d'accéder directement à une adresse mémoire (C, Pascal...). Nil n'indique pas l'adresse... Il indique tout simplement qu'aucune assignation d'adresse et donc aucune réservation d'espace mémoire n'a encore eu lieu.

Conséquence de tout cela, il n'est pas possible de comparer un marqueur comme NULL, car c'est justement une non valeur.

En l'occurence une requête comme celle-ci :

SELECT *
FROM   T_CLIENT
WHERE  CLI_ENSEIGNE = NULL
...est syntaxiquement fausse et conduira la plupart du temps à un message d'erreur.

Sauf que certains SGBDR acceptent délibérement une syntaxe aussi violante.


1.1. Pas de NULL svp !

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 indiquez 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" aux 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é !


1.2. Plus de "NULL" svp ?

Chez les adeptes du NULL, l'idée émergente a longtemps été de rajouter quelques marqueurs supplémentaires : valeur inaplicable, 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 toît 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 ...
Erreur de dépassement arithmétique pour le type de données smallint, valeur = 32768.
Ou bien même, comment représenter une erreur, afin de la traiter "en ligne" dans la requête ?
Se 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...


2. Test des marqueurs NULL

Par défaut, tout prédicat (expression logique évaluable) contenant un marqueur NULL, doit prendre la valeur UNKNOWN...
Alors là, il convient de dire que UNKNOWN est bien une valeur. C'est de la logique 3 état. Autrement dit en SQL, un prédicat peut prendre les valeurs TRUE, FALSE et UNKNOWN.
En principe on devrait même pouvoir construire des prédicats du genre :

SELECT ...
FROM   ...
WHERE  (COLONNE1 = COLONNE2) IS UNKNOWN
Et donc la présente clause WHERE serait vraie si COLONNE1 ou COLONNE2 est marqué à NULL (ou inclusif).
Mais peu de SGBDR ont implémenté un tel test.

Attention à la logique des NULL dans les prédicats...
Ainsi NULL OR TRUE vaut TRUE, NULL OR FALSE vaut FALSE, NULL AND FALSE vaut FALSE

Mais la norme SQL nous fournit différents outils pour tester les marqueurs NULL :


2.1. Test direct : IS NULL, IS NOT NULL

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

2.2. Dénullification : COALESCE

Il est possible de remplacer à la volée, donc dans une requête, un marqueur NULL par une valeur conventionnelle. Par exemlple, 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é à 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.


2.3. Nullification : NULLIF

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.


2.4. Structures de traitement des marqueurs NULL

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

3. Jointures externes

Un autre moyen de bien s'amuser avec les NULL est de faire des jointures externes. Au tout début du SQL, de telles jointures étaient impossibles car la base devait être parfaite ! Hélas, la perfection étant franchement dérangeant dans ce cas, il fallut se rendre à l'évidence : les jointures externes se devaient d'exister et donc les NULL de proliférer.

Pour une étude sur les jointures externes et l'hypothèse du monde clos, lire : Les jointures

Dès lors, avec une jointure externe gauche, la partie des colonnes de la table de droite sans lien avec la table gauche voit les valeurs de toute ses colonnes marquées à NULL. C'est bien entendu l'inverse dans une jointure externe droite.
Avec la jointure externe bilatérale on se retrouve avec des NULL tantôt à droite tantôt à gauche.

CREATE TABLE T1
(I11 INTEGER,
 I12 INTEGER)
 
CREATE TABLE T2
(I21 INTEGER,
 I22 INTEGER)
 
INSERT INTO T1 VALUES (NULL, 1)
INSERT INTO T1 VALUES (1, NULL)
INSERT INTO T1 VALUES (1, 1)
INSERT INTO T1 VALUES (NULL, NULL)
 
INSERT INTO T2 VALUES (NULL, NULL)
INSERT INTO T2 VALUES (1, NULL)
INSERT INTO T2 VALUES (NULL, 1)
INSERT INTO T2 VALUES (1, 1)
INSERT INTO T2 VALUES (1, 2)
INSERT INTO T2 VALUES (2, 1)
INSERT INTO T2 VALUES (2, 2)
INSERT INTO T2 VALUES (2, NULL)
INSERT INTO T2 VALUES (NULL, 2)
 
SELECT *
FROM   T1
       LEFT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
1           1           1           1
1           NULL        NULL        NULL
NULL        1           NULL        NULL
NULL        NULL        NULL        NULL
 
SELECT *
FROM   T1
       RIGHT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
1           1           1           1
NULL        NULL        2           2
NULL        NULL        2           1
NULL        NULL        2           NULL
NULL        NULL        1           2
NULL        NULL        1           NULL
NULL        NULL        NULL        2
NULL        NULL        NULL        1
NULL        NULL        NULL        NULL
 
SELECT *
FROM   T1
       FULL OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
1           1           1           1
1           NULL        NULL        NULL
NULL        1           NULL        NULL
NULL        NULL        2           2
NULL        NULL        2           1
NULL        NULL        2           NULL
NULL        NULL        1           2
NULL        NULL        1           NULL
NULL        NULL        NULL        2
NULL        NULL        NULL        1
NULL        NULL        NULL        NULL
NULL        NULL        NULL        NULL
 
SELECT *
FROM   T1
       UNION JOIN T2
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
NULL        1           NULL       NULL
1           NULL        NULL       NULL
1           1           NULL       NULL
NULL        NULL        NULL       NULL
NULL        NULL        NULL       NULL
NULL        NULL        1          NULL
NULL        NULL        NULL       1
NULL        NULL        1          1
NULL        NULL        1          2
NULL        NULL        2          1
NULL        NULL        2          2
NULL        NULL        2          NULL
NULL        NULL        NULL       2
Enfin, avec le UNION JOIN on se retrouve avec un résultat dont l'apparence est un genre de matrice dont seule la diagonale est alimentée par les valeurs originales des tables, toutes les autres parties étant nullifiées.

Cette petite différence, va faire des choses bien intéressante lorsque l'on va rajouter un filtre WHERE...

Voici un exemple de ce qui vous attend :

SELECT *
FROM   T1
       LEFT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
WHERE  I21 < 2
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
1           1           1           1
 
SELECT *
FROM   T1
       LEFT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
               AND I21 < 2
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
NULL        1           NULL        NULL
1           NULL        NULL        NULL
1           1           1           1
NULL        NULL        NULL        NULL
Suivant que l'on filtre dans la clause WHERE ou dans le prédicat de jointure, le résultat n'est pas le même. C'est logique car le filtrage s'applique APRÈS la jointure. Autrement dit la jointure externe conserve ses NULL.

Pour remédier à l'un ou à l'autre on peut faire :

SELECT *
FROM   T1
       LEFT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
WHERE I21 alt; 2 OR I21 IS NULL
 
I11         I12         I21         I22        
----------- ----------- ----------- -----------
NULL        1           NULL        NULL
1           NULL        NULL        NULL
1           1           1           1
NULL        NULL        NULL        NULL
 
SELECT *
FROM   T1
       LEFT OUTER JOIN T2
             ON T1.I11 = T2.I21
                AND  T1.I12 = T2.I22
                AND I21 < 2
WHERE I21 IS NOT NULL
 
I11         I12         I21         I22         
----------- ----------- ----------- -----------
1           1           1           1
Ou encore utiliser des fonctions et structures de denullifiation comme indiqué en 2.2 et 2.3


4. Traitement des marqueurs NULL

Mais comment SQL utilise t-il les marqueurs NULL dans ses traitements ? C'est de cela que nous allons maintenant parler...

On dit que les marqueurs NULL se propagent dans les expressions. Certains auteurs le qualifient "d'absorbant"...

Exemple :

SELECT 1 + NULL
FROM ...
 
----------------
NULL
En conclusion, quelque soit l'expression, même si elle est calculée, elle vaudra UNKNOWN en présence d'un ou de plusieurs marqueurs NULL.


4.1. Déclaration de variable dans le code procédurale SQL : DECLARE

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.


4.2. Les calculs d'agrégats statistiques ignorent les marqueurs NULL : COUNT, SUM, AVG, MAX ou MIN

Par exception à la régle de propagation des NULL, l'absence de valeur dans une colonne n'a pas pour effet d'entâcher 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 occurences de la colonne sont NULL, COUNT renvoi 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...


4.3. Le comptage des lignes de la table prend en compte les lignes NULL ! : COUNT(*)

COUNT(*) est un calcul d'agrégat un peut particulier dans le sens ou il compte le nombre de lignes que possède la table. Dans ce cas, même si la ligne voit toute 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 1ère et 10ème lignes entièrement à NULL ont bien été comptées !


4.4. NULL existe bien !

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 !)...


4.5. L'ordre du NULL...

Une question intérressante est de savoir dans quel ordre doivent se trouver les marqueurs NULL ?
Comme il ne s'agit pas de valeurs, une clause tel 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'ordonancement des NULL qui peut prendre la valeur NULL FIRST ou NULL LAST.

Le problème c'est que peut d'éditeur ont optés pour un tel rafinement. 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."


4.6. NULL avec DISTINCT et UNION

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 que 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érentes 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"...


5. Définition de table et contraintes

Dans la création des tables d'une base de données, beaucoups d'éléments reposent sur la problématique du traitement des NULL.

Nous allons en voir quelques unes...


5.1. Contrainte de colonne NUL / NOT NULL

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

En l'occurence, 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.


5.2. Contrainte d'unicité

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 tuple contraint n'ayant pas de valeurs 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 5eme 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 !


5.3. Autres contraintes

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 ou l'un des colonne possède un marqueur NULL, la contrainte est violée.
A lire sur le sujet : ../sqlaz/ddl/?page=partie2#L7.3.1
Mais peu de SGBDR vont jusque là dans la gestion de l'intégrité référentielle.


6. Mise à jour avec les NULL

SQL nous facilite la vie à l'aide des NULL pour les insertions et les mises à jour...


6.1. NULL et INSERT

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 le liste des colonnes cible.

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

6.2. NULL et UPDATE

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.


7. Particularité de certains SGBDR


7.1. Autres fonctions de traitement des NULL

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 2 arguments. On trouve également en alternative sous SQL Server une fonction ISNULL.


7.2. NULL comme contrainte par défaut

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 !


7.3. NULL et CHAR

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 !


7.4. NULL et UNIQUE

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.


7.5. Jointures externes et anciennes syntaxes

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 une deux requêtes logiquement équivalente, l'une basée sur la syntaxe des jointures normative et l'autre sur l'ancienne syntaxe en =*... Les jointures


7.6. Prédicat = NULL !

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

8. Références bibliographiques

Incomplete and Uncertain Information in Relational Databases - Esteban ZIMANYI - Université Libre de Bruxelles : http://cs.ulb.ac.be/publications/PhD-92-01.pdf

NULL Handling in SQLite Versus Other Database Engines : http://www.hwaci.com/sw/sqlite/nulls.html

ANSI/X3/SPARC, "Study Group on Data Base Management Systems", Interim Report 75-02-08, FDT Bulletin of ACM SIGMOD, Volume 7-2, February 1975.

SQL for smarties - Joe Celko - Morgan Kaufmann

SQL Performance Tuning - Peter Gulutzan & Trudy Pelzer - Addison Wesley

SQL:1999 - Jim Melton - Morgan Kaufman


Remerciements à Braim pour la relecture orthographique de cet article, ainsi qu'aux membres de la rubrique SGBD.


Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

Copyright © 2004 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Cette page est déposée.

 
 
Developpez.com

Nous contacter

Participez

Informations légales

 
Services

Forum SGBD & SQL

Blogs

Hébergement

 
Partenaires

Hébergement Web