SQL IS NULL et IS NOT NULL

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

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 : elle compte 16044 lignes, et return_date est NULL pour les films qui n'ont pas encore été rendus.

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 rental_id, rental_date, return_date
FROM rental
WHERE return_date = NULL;

Résultat : aucune ligne, 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 rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NULL;

Résultat : 183 lignes, les locations dont les films sont toujours sortis.

IS NULL se combine avec d'autres conditions comme n'importe quelle comparaison :

SELECT rental_id, rental_date, return_date
FROM rental
WHERE customer_id = 554 AND return_date IS NULL;

Résultat : une ligne, la location 14098 du 2005-08-21 : le client 554 a toujours ce film.

IS NOT NULL

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

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NOT NULL;

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

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. Comparez deux requêtes :

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01';

Résultat : 8207 lignes.

SELECT rental_id, return_date
FROM rental
WHERE NOT (return_date >= '2005-08-01');

Résultat : 7654 lignes. 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 rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Résultat : 8390 lignes = 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 title, language_id
FROM film
WHERE language_id NOT IN (2, 3);

Résultat : 1000 lignes, tous les films.

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, NULL);

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

Personne n'écrit NULL à la main dans une liste, mais plus loin dans le cours la liste viendra 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')
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21';

Résultat : 4 locations ; la location 14098 affiche « not returned ». Sans nom, la colonne porte comme titre l'expression elle-même.

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 ; les deux se combinent avec AND et OR.
• 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.

Peut-on combiner IS NULL avec AND et OR ?

Oui, comme toute autre condition : WHERE customer_id = 554 AND return_date IS NULL trouve les films qu'un client a encore, et OR return_date IS NULL ajoute à une autre condition les lignes sans valeur.

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.