Le `NULL` en SQL est étrange

• En SQL, les valeurs NULL sont traitées de manière particulière. Une colonne avec une contrainte UNIQUE peut contenir plusieurs valeurs NULL.

  • Cela vient du fait que chaque valeur NULL est considérée comme une valeur indépendante, différente des autres NULL
  • SQLite, Postgres et MySQL se comportent tous de la même manière.

• Établir une référence

  select '' = '';    -- Returns 1 (true) les chaînes vides sont égales   
  select 1 = 1;      -- Returns 1 (true) les nombres sont égaux   
  select 1 = 0;      -- Returns 0 (false) les nombres sont différents   
  select null = null; -- Returns NULL (null) Hein ?  
  

  • Comme NULL est un espace réservé qui représente une « valeur inconnue », deux valeurs inconnues ne sont pas considérées comme égales
  • En utilisant l’opérateur IS, on peut vérifier l’identité de NULL. Par exemple, null is null renvoie TRUE.

• À propos de l’unicité

  • Quand une colonne avec une contrainte UNIQUE contient des valeurs NULL, ces valeurs NULL sont considérées comme différentes les unes des autres et ne violent donc pas la contrainte d’unicité.
  • Par exemple, ('[e-mail]', NULL) et ('[e-mail]', NULL) sont considérées comme deux lignes différentes.

• Pourquoi NULL est traité ainsi

  • SQLite et les autres bases de données compatibles SQL sont implémentées de cette façon pour gérer NULL de manière cohérente avec les autres bases de données. La documentation du standard SQL suggère que NULL devrait être unique partout, mais en pratique la plupart des moteurs SQL ne traitent pas NULL comme unique dans SELECT DISTINCT ou UNION.

• Comment garantir l’unicité

  • Utiliser une colonne générée

    • On peut atténuer le problème en créant une colonne qui a toujours une valeur déterministe non nulle. Par exemple, on peut remplacer les valeurs NULL avec COALESCE(deleted_at, '1970-01-01').
    • Cette méthode peut occuper de l’espace en ajoutant un champ supplémentaire à la table.

  • Utiliser un index partiel

    • On peut garantir l’unicité en créant un index partiel sur email uniquement lorsque deleted_at vaut NULL.
    • Un index partiel élargit moins la table, prend moins d’espace et ne produit pas d’erreur lorsqu’on supprime de manière répétée la même paire d’enregistrements.

• Mise à jour

  • Oracle traite les chaînes vides comme NULL.

• Conclusion

  • C’est invisible lorsqu’on utilise un ORM, mais la façon particulière dont SQL traite NULL peut être source de confusion. La documentation du standard SQL n’est pas disponible publiquement et n’est accessible que contre paiement.

[iolothebard]

Tous les null sont étranges.
Du coup, le null tout à fait normal de SQL finit plutôt par paraître étrange…
Au royaume des borgnes, celui qui a deux yeux est anormal…

[GN⁺]

Commentaires Hacker News

• Le NULL de SQL repose sur la logique TRUE-FALSE-UNKNOWN de Kleene. Si l’on lit NULL comme UNKNOWN, de nombreuses opérations deviennent plus intuitives à comprendre

  • TRUE OR UNKNOWN = TRUE, TRUE AND UNKNOWN = UNKNOWN, UNKNOWN XOR UNKNOWN = UNKNOWN, etc.
  • NULL est un espace réservé représentant UNKNOWN, et on ne peut pas dire que deux NULL sont égaux
  • Depuis Postgresql 15, on peut créer des index uniques avec NULLS NOT DISTINCT

• Lorsque le concept de NULL a été introduit dans les années 1970, on pensait déjà qu’il provoquerait beaucoup de confusion à l’avenir. Quarante-cinq ans plus tard, le sujet fait toujours débat

• Compréhension intuitive de NULL : une valeur NULL dans une cellule d’une table est une manière d’indiquer « pas de valeur ». Lorsqu’on veut des valeurs uniques, les cas où il n’y a pas de valeur ne devraient pas être pris en compte

• Scepticisme vis-à-vis de l’utilisation des ORM : les ORM sont pratiques, mais ils ont produit une génération qui n’a pas appris le fonctionnement réel des bases de données relationnelles. Le comportement de SQL NULL est cohérent avec l’algèbre relationnelle de base, et c’est le NULL de style C qui pose problème

• Cela rappelle l’humour d’une réplique d’un épisode de Blackadder sur la comparaison de NULL

• Le fait qu’Oracle considère NULL comme égal à une chaîne vide semble étrange

• Dans un contexte orienté objet, "null" est utile pour indiquer qu’une propriété donnée n’a pas de valeur. En JavaScript, il y a null et undefined ; on peut considérer que undefined signifie que la valeur est inconnue, tandis que null signifie qu’il n’y a pas de valeur

• NULL n’est pas étrange au sens où il n’implique pas de doublons. Comme les NULL ne sont pas égaux entre eux, ils ne peuvent pas constituer des doublons. Si l’on n’aime pas la sémantique de NULL, on peut utiliser une valeur sentinelle

• Le NULL de SQL n’est pas étrange si l’on réfléchit à la manière dont la logique relationnelle devrait fonctionner dans des enregistrements contenant des valeurs inexistantes