Leçon 2.4 · Temps de lecture : ~6 min

IS NULL et IS NOT NULL sont le seul moyen fiable de filtrer les valeurs manquantes en SQL. Dans cette leçon, vous verrez pourquoi WHERE column = NULL ne trouve rien, comment trouver les lignes avec ou sans valeur et comment NULL retire discrètement des lignes des conditions écrites avec NOT, <> et NOT IN. Les exemples utilisent la table rental de la base Sakila, où return_date est NULL pour les films qui n'ont pas encore été rendus.

SQL IS NULL et IS NOT NULL

Pourquoi = NULL ne trouve rien

NULL signifie « la valeur est inconnue ». Toute comparaison avec une valeur inconnue est elle aussi inconnue : return_date = NULL n'est ni vrai ni faux, c'est UNKNOWN. WHERE ne garde que les lignes où la condition est TRUE, la requête ne renvoie donc rien :

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date = NULL;

Résultat : 0, alors que 183 locations n'ont pas de date de retour.

Il en va de même avec <>, >, < et tout autre opérateur de comparaison.

IS NULL

IS NULL est un test spécial qui vaut TRUE quand la valeur manque :

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date IS NULL;

Résultat : 183.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date
LIMIT 3;

Résultat : les locations 14098 (client 554), 13421 (client 366) et 15542 (client 111), les plus anciens films toujours sortis.

IS NOT NULL

IS NOT NULL est l'inverse : TRUE quand la colonne a une valeur.

SELECT COUNT(*) AS returned FROM rental WHERE return_date IS NOT NULL;

Résultat : 15861. Avec les 183 locations sans date de retour, cela fait les 16044 lignes.

NULL échappe à une condition et à son NOT

On pense naturellement qu'une condition et son NOT coupent une table en deux. Avec NULL, ce n'est pas le cas :

SELECT
    (SELECT COUNT(*) FROM rental WHERE return_date >= '2005-08-01') AS after_august,
    (SELECT COUNT(*) FROM rental WHERE NOT (return_date >= '2005-08-01')) AS before_august,
    (SELECT COUNT(*) FROM rental) AS total;

Résultat : 8207 + 7654 = 15861, et non 16044. Pour les 183 lignes sans date de retour, les deux conditions valent UNKNOWN, et NOT UNKNOWN vaut toujours UNKNOWN.

Pour garder ces lignes, demandez-les explicitement avec OR ... IS NULL :

SELECT COUNT(*) AS late_or_unknown
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Résultat : 8390 = 8207 + 183.

Le piège de NOT IN

NOT IN est une suite de comparaisons <> reliées par AND. Si la liste contient NULL, l'une d'elles vaut toujours UNKNOWN et aucune ligne ne passe :

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, 3);

Résultat : 1000.

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, NULL);

Résultat : 0, car language_id <> NULL vaut UNKNOWN pour chaque film.

Personne n'écrit NULL à la main dans une liste, mais la liste vient souvent d'une colonne, et un seul NULL suffit à vider le résultat. Avant NOT IN, assurez-vous que les valeurs ne peuvent pas être NULL.

Afficher une valeur à la place de NULL

COALESCE renvoie le premier de ses arguments qui n'est pas NULL. C'est pratique pour afficher une valeur lisible dans le résultat :

SELECT rental_id, rental_date, COALESCE(return_date, 'not returned') AS returned_at
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21'
ORDER BY rental_date;

Résultat : 4 locations ; la première, 14098, affiche « not returned ».

C'est du MySQL. PostgreSQL et SQL Server exigent deux arguments du même type : la date y est donc d'abord convertie en texte. COALESCE et les autres fonctions sont présentées dans le module sur les fonctions.

Points clés :

• Toute comparaison avec NULL (=, <>, >, <) donne UNKNOWN, et WHERE écarte ces lignes.
• Utilisez IS NULL pour trouver les lignes sans valeur et IS NOT NULL pour celles qui en ont une.
• Une condition et son NOT ne couvrent pas à eux deux les lignes où la colonne est NULL ; ajoutez OR column IS NULL pour les garder.
• NOT IN ne renvoie aucune ligne si la liste contient NULL.
• COALESCE(column, value) affiche une valeur de remplacement à la place de NULL.

Questions fréquentes

Pourquoi WHERE column = NULL ne renvoie-t-il aucune ligne ?

Une comparaison avec NULL vaut UNKNOWN, et non TRUE, et WHERE ne garde que les lignes TRUE. Utilisez WHERE column IS NULL.

IS NOT NULL équivaut-il à NOT (column = NULL) ?

Non. column = NULL vaut UNKNOWN, et NOT UNKNOWN vaut aussi UNKNOWN : NOT (column = NULL) ne trouve donc rien non plus. IS NOT NULL vaut TRUE pour chaque ligne qui a une valeur.

COUNT(column) compte-t-il les valeurs NULL ?

Non. COUNT(*) compte les lignes, COUNT(column) ne compte que les valeurs non NULL de la colonne. Les fonctions d'agrégation ont leur propre module.

Questions d'entretien

Pourquoi les requêtes SQL utilisent-elles IS NULL et non = NULL ?

NULL signifie une valeur inconnue, et comparer quoi que ce soit à une valeur inconnue donne UNKNOWN, et non TRUE ou FALSE. WHERE ne garde que les lignes où la condition vaut TRUE, donc = NULL ne trouve jamais rien. IS NULL est un test à part qui renvoie TRUE ou FALSE.

Pourquoi NOT IN peut-il renvoyer un résultat vide ?

x NOT IN (a, b) signifie x <> a AND x <> b. Si la liste contient NULL, x <> NULL vaut UNKNOWN pour chaque ligne, l'ensemble du AND ne peut pas être TRUE et aucune ligne n'est renvoyée. Cela arrive quand la liste vient d'une sous-requête dont la colonne contient des NULL.

Comment garder les lignes avec NULL dans un filtre ?

Ajoutez explicitement le cas NULL : WHERE return_date >= '2005-08-01' OR return_date IS NULL. Sinon, les lignes avec NULL sont écartées à la fois par la condition et par sa négation.