Pourquoi NULL ne se compare pas comme zéro en SQL

Trois lignes, deux soldes connus

Imaginez trois soldes : 100, 0 et NULL. Il existe trois comptes, mais seulement deux soldes connus. Le troisième peut être nul, négatif ou positif ; les données ne permettent pas de trancher. Une valeur absente ne prouve donc pas l’absence d’argent.

La requête ci-dessous utilise VALUES et ne nécessite aucune table. Elle donne total_rows = 3, known_balances = 2 et mean_known = 50. Remplacer le manque par zéro produit mean_with_zero ≈ 33,33. Le dénominateur change : vous modifiez le sens de l’indicateur, pas seulement l’affichage.

WITH balances(balance) AS (
  VALUES (100::numeric), (0::numeric), (NULL::numeric)
)
SELECT COUNT(*) AS total_rows,
       COUNT(balance) AS known_balances,
       AVG(balance) AS mean_known,
       AVG(COALESCE(balance, 0)) AS mean_with_zero
FROM balances;

Rechercher les valeurs manquantes

Un solde inconnu n’est pas nécessairement nul. PostgreSQL propose IS NOT DISTINCT FROM pour comparer les valeurs tout en considérant deux NULL comme égaux.

SELECT * FROM accounts WHERE balance IS NULL;
SELECT a IS NOT DISTINCT FROM b;

Compter ce qui vous intéresse

COUNT(*) compte les lignes. COUNT(balance) compte les soldes non NULL. COALESCE(balance, 0) remplace une valeur manquante par zéro ; faites-le seulement si cette substitution correspond au sens des données.

Pourquoi un filtre fait disparaître des lignes

Sur ces données, WHERE balance <> 0 ne conserve que 100. Le zéro rend la condition fausse ; NULL la rend inconnue. Pour garder les soldes non nuls ainsi que les valeurs manquantes, écrivez balance <> 0 OR balance IS NULL.

La négation ne récupère pas les valeurs absentes : NOT(balance = 0) reste inconnue pour NULL. Formulez d’abord la règle applicable aux données manquantes, puis son expression SQL. Examinez aussi les lignes écartées, surtout lorsque les totaux du rapport deviennent inattendus.

Le piège de NOT IN avec NULL

Vous voulez exclure l’identifiant 2, mais la liste contient aussi NULL. Même 1 NOT IN (2, NULL) est inconnu : SQL ne peut pas prouver que 1 diffère de chaque élément. Une condition WHERE utilisant cette expression élimine la ligne.

Pour une exclusion par sous-requête, envisagez NOT EXISTS avec un critère de correspondance explicite. Cette construction vérifie l’absence d’une ligne correspondante sans reprendre le NULL de la liste. Décidez néanmoins comment traiter les identifiants manquants : la syntaxe ne définit pas votre règle métier.

SELECT 1 NOT IN (2, NULL) AS result;

Les valeurs absentes après un LEFT JOIN

LEFT JOIN conserve les lignes de gauche et fournit NULL pour les colonnes de droite sans correspondance. Une restriction sur une colonne de droite dans WHERE peut supprimer ces lignes. Pour garder tous les comptes et ne joindre que les enregistrements actifs, placez cette restriction dans ON.

Avant COALESCE, identifiez l’origine du manque : information non recueillie, champ sans objet ou absence de correspondance ? Ces cas peuvent demander des libellés différents. Comptez les lignes, les valeurs connues et les manques ; examinez les jointures ; choisissez ensuite une substitution adaptée au rapport.