Une requête s’exécute sans erreur et renvoie un nombre qui semble raisonnable, mais des lignes manquent et la moyenne est fausse. Les valeurs SQL NULL en sont très souvent la cause. NULL ne se comporte pas comme n’importe quelle valeur normale, ce qui surprend aussi bien les développeurs débutants que expérimentés.

Ce guide montre exactement comment NULL se comporte. Nous avons exécuté chaque requête dans SQLite 3.50 et les résultats affichés proviennent de cette exécution. La plupart de ce comportement est du SQL standard, et nous notons les différences entre les bases de données, comme l’ordre de tri NULL et le traitement des chaînes vides par Oracle.

Qu’est-ce que NULL ?

NULL est un marqueur signifiant qu’une valeur est manquante ou inconnue. Ce n’est pas une valeur au sens habituel du terme, ce sont donc toutes des choses différentes :

La différence est particulièrement importante pour les montants. Un bonus de 0 signifie que la personne n’a rien reçu. Un bonus NULL signifie que personne ne le sait encore.

Le tableau exemple

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Notez que Sara a une chaîne vide pour le téléphone, alors qu’Arjun et Ravi ont NULL. Les deux sont traités très différemment.

Test pour NULL

Voici l’erreur que presque tout le monde fait au début :

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

La première requête est du SQL valide et ne donne aucune erreur. Cela ne renvoie tout simplement rien, car comparer quoi que ce soit avec NULL n’est jamais vrai. Sara apparaît sous IS NOT NULL, puisqu’une chaîne vide est une valeur réelle. Dans Oracle, cependant, une chaîne vide est traitée comme NULL, ce qui constitue une exception notable.

Logique à trois valeurs

Dans la plupart des langages de programmation, une condition est vraie ou fausse. SQL ajoute un troisième résultat : inconnu. Si vous ne connaissez pas une valeur, vous ne pouvez pas dire si elle est égale à 5. Et on ne sait pas que deux valeurs inconnues sont égales, c’est pourquoi NULL = NULL n’est pas vrai. Lors de notre test, tous ces éléments ont renvoyé NULL :

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

La combinaison des conditions suit des règles rigoureuses. Voici les résultats de notre analyse, où 1 est vrai et 0 est faux :

Lignes qui disparaissent de OÙ

WHERE conserve une ligne uniquement si sa condition est true. Les lignes dont le résultat est inconnu sont supprimées, ainsi que celles qui sont fausses. Cela conduit à des résultats surprenants :

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Vous pourriez vous attendre à ce qu’Arjun et Ravi apparaissent, puisque leur téléphone n’est certainement pas ce numéro. Mais leur téléphone est inconnu, donc le résultat est inconnu, et ils disparaissent. Pour les inclure, dites-le explicitement : WHERE phone <> '555-0101' OR phone IS NULL. La négation n’aide pas non plus :

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara et Ravi n’ont aucun bonus, ils n’apparaissent donc ni dans bonus > 400 ni dans son contraire. C’est un élément clé à retenir : une condition et sa négation ne couvrent pas toujours chaque ligne.

COALESCE : fournir une solution de repli

COALESCE renvoie la première valeur non NULL de sa liste et fonctionne sur toutes les principales bases de données :

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara apparaît toujours vide, car une chaîne vide n’est pas NULL. Des fonctions similaires existent, telles que IFNULL dans MySQL et SQLite, ISNULL dans SQL Server et NVL dans Oracle. COALESCE est le choix portable. Notre point précédent sur les calculs s’applique également ici. bonus + 100 donne NULL pour toute personne sans bonus, alors écrivez COALESCE(bonus, 0) + 100 si traiter un bonus manquant comme zéro est correct pour votre entreprise.

NULLIF : transforme une valeur en NULL

NULLIF(a, b) renvoie NULL si les deux valeurs sont égales, et sinon renvoie a. Deux utilisations sont particulièrement utiles. Il peut nettoyer les valeurs d’espace réservé :

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Cette deuxième requête est un moyen pratique de rechercher des valeurs « manquantes ou vides ». Il peut également se prémunir contre la division par zéro, qui génère une erreur dans de nombreuses bases de données :

SELECT total / NULLIF(quantity, 0) FROM sales;

Lorsque quantity vaut 0, cela donne NULL plutôt que d’échouer. Dans notre test, SELECT 100 / NULLIF(0, 0) a renvoyé NULL.

Les agrégats ignorent NULL

C’est ici que NULL cause le plus de dégâts aux rapports :

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Alors, quel est le bonus moyen, 400 ou 200 ? Cela dépend de ce que NULL signifie dans vos données. Si NULL signifie “aucun bonus n’a été payé”, alors 200 est correct. Si cela signifie « pas encore décidé », alors 400 pourrait être correct. La base de données ne peut pas prendre cette décision à votre place, la question doit donc être adressée à quelqu’un qui connaît l’entreprise.

Un piège associé : SUM sans valeur est NULL plutôt que 0. Lorsque nous avons additionné uniquement les lignes sans bonus, nous avons obtenu NULL, tandis que COUNT a renvoyé 0. Le code d’application attendant un nombre peut planter là-dessus, alors enveloppez-le : COALESCE(SUM(bonus), 0).

GROUP BY, tri et DISTINCT

Bien que les valeurs NULL ne soient « pas égales » dans les comparaisons, ces opérations les traitent comme un seul groupe :

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Voir SQL GROUP BY et HAVING pour en savoir plus sur le regroupement. L’ordre de tri est l’endroit où les bases de données diffèrent vraiment. SQLite et MySQL traitent NULL comme la plus petite valeur, tandis que PostgreSQL et Oracle la traitent comme la plus grande. Ainsi, le même ORDER BY bonus DESC peut mettre les NULL en dernier dans l’un et en premier dans un autre. PostgreSQL, Oracle et SQLite moderne vous permettent de l’indiquer directement, en utilisant NULLS FIRST ou NULLS LAST.

Rejoint

Une condition de jointure utilisant = ne correspond jamais à NULL, donc les lignes avec une clé NULL sont supprimées d’une jointure interne :

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera n’a pas de gestionnaire, donc une jointure interne supprime silencieusement cette ligne. Un LEFT JOIN le conserve et crée également ses propres NULL pour le côté manquant. Pour en savoir plus, consultez Explication des jointures SQL.

Le piège NOT IN

Un seul NULL dans une liste NOT IN efface tous les résultats :

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

La deuxième forme signifie manager_id <> 1 AND manager_id <> NULL, et cette dernière partie ne peut jamais être vraie. Ceci est particulièrement dangereux avec les sous-requêtes, où un NULL peut arriver inaperçu. Utilisez plutôt NOT EXISTS, comme expliqué dans notre Tutoriel sur les sous-requêtes SQL. Meera est également absent du premier résultat, puisque le manager_id de cette ligne est NULL.

Concevoir avec NULL

• Utilisez NOT NULL par défaut. Autorisez NULL uniquement lorsqu’une valeur peut vraiment manquer.
• Ajoutez des valeurs par défaut raisonnables. Par exemple, quantity INT NOT NULL DEFAULT 0.
• N’utilisez pas de valeurs magiques. Une date du 01/01/1900 ou un âge de -1 remplaçant “inconnu” corrompt vos calculs.
• Connaître le comportement UNIQUE. La plupart des bases de données autorisent plusieurs NULL dans une colonne UNIQUE, et notre test en a inséré deux sans erreur. SQL Server est une exception, car il en autorise normalement une.
• N’oubliez pas qu’une clé primaire ne peut pas être NULL. Voir clé primaire vs clé étrangère.
• Test avec des NULL. Les données de test sans NULL masquent ces bugs. Incluez-les volontairement, comme vous le feriez pour d’autres cas extrêmes.

Pour conclure

Traitez NULL comme “inconnu”, et la plupart de son comportement commence à avoir un sens : inconnu plus un est inconnu, et inconnu égal inconnu n’est pas connu pour être vrai. Auditez l’un de vos propres rapports cette semaine. Vérifiez les colonnes nullables en utilisant COUNT(*) par rapport à COUNT(column), recherchez n’importe quel NOT IN et décidez ce que NULL devrait signifier dans chaque moyenne.

Questions fréquentes

Continuez à apprendre