Le piège du null

Plantons le décor:

Une table "dépenses" créée avec l'instruction:

CREATE TABLE dépenses
(référence CHAR(9) PRIMARY KEY,
 date_exec DATE,
 montant NUMERIC(13,2),
 détails VARCHAR(50),
 code_u CHAR(2))
;

et dans laquelle quelques données ont été placées.

un code_u dont la signification est:

 code_u | signification     
--------+----------------
 01     | Médecin
 02     | Grande surface
 03     | Carburant
 04     | Restaurant

Voilà, le décor est planté.

Examinons le contenu de la table "dépenses", par exemple depuis un terminal psql si nous travaillons avec PostgreSQL:

Nous désirons mettre à jour code_u avec cette instruction:

UPDATE dépenses
SET code_u = '02'
WHERE (détails = 'Courrefar'
OR détails = 'Caro'
OR détails = 'Aupré'
)
AND code_u <> '02'
;

La ligne AND est là pour éviter les mises à jour inutiles (mise à jour des rangées avec code_u valant déjà '02').
En principe 3 rangées devraient être impactées: celles dont la référence se termine par 17, 21 et 22.
Vérifions s'il en sera bien ainsi:

Il manque la rangée 2013-0022. Pourtant rien ne semble la distinguer (par rapport aux critères de sélection) de la rangée 2013-0021.
Poussons plus loin  les investigations.
L'utilisation de la méta-commande \pset null nous permet de choisir ce qui sera imprimé dans le cas de la valeur NULL. Par défaut rien n'est imprimé

Nous voyons maintenant apparaître une différence essentielle entre les rangées 2013-0021 et 2013-0022. Pour la rangée 2013-0021, code_u est caractérisé par une chaîne de caractère vide, ce qui permet de dire que code_u n'est pas égal à 01, ni à 02, 03, 04.(ou sinon la chaîne ne serait pas vide).
Par contre dans le cas de la rangée 2013-0022, l'indication NULL signifie que la base de données ne contient aucune information concernant code_u. Pour cette rangée, code_u est inconnu: il peut très bien valoir '02', où pas. Il est donc impossible de mettre cette rangée dans l'ensemble de celles dont le code_u vaut '02' ou dans l'ensemble de celles dont le code_u est différent de '02'. L'union des 2 ensembles nous fournit 7 rangées (au lieu de 9):

Si le critère de sélection est code_u = '02', les rangées avec code_u NULL se sont pas reprises. Mais elles ne le sont pas non plus si le critère de sélection est code_u différent de '02'.
Et voilà le piège, le piège du NULL dans lequel il ne faut pas tomber! Y tomber, c'est être nul!
L'instruction à utiliser pour la mise à jour devrait être:

UPDATE dépenses
SET code_u = '02'
WHERE (détails = 'Courrefar'
OR détails = 'Caro'
OR détails = 'Aupré'
)
AND (code_u <> '02'
OR code_u IS NULL
)
;

Dans le cas d'une base de données avancée comme PostgreSQL, qui gère l'intégrité référentielle, nous aurions pu après avoir créé une table destinée à contenir les significations de code_u:

CREATE TABLE utilisations
(code_u CHAR(2) PRIMARY KEY,
 signification VARCHAR(20))
;

créer la table "dépenses" comme suit:

CREATE TABLE dépenses
(référence CHAR(9) PRIMARY KEY,
 date_exec DATE,
 montant NUMERIC(13,2),
 détails VARCHAR(50),
 code_u CHAR(2) REFERENCES utilisations)
;

Dans ce cas les seules valeurs autorisées pour code_u auraient été celles qui figurent dans la table "utilisations". Une rangée comme la 2013-0021 n'aurait pas pu exister, mais bien des rangées avec code_u à la valeur NULL.
La notion d'intégrité référentielle a été étudiée dans ce billet.