Comment utiliser les opérateurs de comparaison avec les NULL en SQL

La valeur SQL NULL a une utilité particulière. Elle s'accompagne également de comportements contre-intuitifs qui peuvent faire trébucher les débutants en SQL ou même les programmeurs expérimentés. Apprenez à éviter ces problèmes lorsque vous utilisez NULL avec des opérateurs de comparaison.

Cet article va vous aider à maîtriser les meilleures pratiques pour élaborer des requêtes SQL qui fonctionnent avec des valeurs NULL et utilisent des opérateurs de comparaison ( =, <>, <, > ) - ce qui, si vous avez déjà écrit des requêtes SQL, représente pratiquement toutes les requêtes que vous écrirez ! Il s'agit d'une connaissance essentielle et sa maîtrise rendra le langage SQL plus facile pour vous.

Nous allons faire un tour rapide des NULLs SQL, de leur raison d'être et de l'impact de leur bizarrerie sur les résultats des requêtes. Nous verrons ensuite quelques requêtes SQL qui illustrent cette bizarrerie. Nous aborderons ensuite les techniques standard pour écrire des requêtes qui traitent correctement les NULL et les opérateurs de comparaison. Enfin, nous ferons un résumé rapide de tous les opérateurs de comparaison de SQL et de la manière dont chacun d'entre eux interagit avec les NULL.

Ne vous inquiétez pas. Ce n'est pas aussi difficile que cela en a l'air. Commençons par passer en revue les valeurs NULL en SQL.

NULL en SQL - Le connu et l'inconnu

Les bases de données sont censées être une source unique de vérité. Ce qui est enregistré dans les champs de chaque ligne représente ce qui est connu.

Regardez le tableau ci-dessous, qui a été adapté de What Is a NULL in SQL (une excellente ressource pour une plongée en profondeur dans les NULL SQL). Ce tableau pourrait faire partie d'une base de données créée par un fan des Simpsons.

Le NULL représente ici deux choses différentes. Waylon Smithers est connu pour être célibataire, donc NULL dans cette ligne dans la colonne du conjoint représente une valeur inexistante. Mais nous savons si peu de choses sur Dr. Nick que les valeurs NULL dans ses colonnes conjoint et status représentent une valeur inconnue.

Pour maintenir l'intégrité d'une base de données, les deux interprétations de NULL sont nécessaires. Et pour aider les programmeurs à maintenir cette intégrité malgré les valeurs manquantes et inconnues, SQL intègre les NULL dans sa logique ternaire.

Qu'est-ce que la logique ternaire et comment fonctionne-t-elle en SQL ?

La logique binaire utilise deux valeurs : Vrai et Faux, 0 et 1, etc. La logique ternaire utilise trois valeurs. En SQL, ces trois valeurs sont Vrai, Faux et Inconnu. En logique ternaire SQL, NULL équivaut à la valeur inconnue.

Voici comment les trois valeurs de la logique ternaire fonctionnent avec les opérateurs logiques SQL NOT, OR et AND:

Ce qu'il faut retenir : Toute opération logique impliquant un NULL donne une valeur inconnue , sauf pour TRUE OR NULL et FALSE AND NULL. Intéressant, mais pas quelque chose que vous devez mémoriser. Comme vous le verrez, SQL nous aide à contourner cette logique délicate. Ce qui est pratique, car les opérations de comparaison SQL utilisent la même logique ternaire.

Opérations de comparaison SQL et valeurs NULL

Voici une requête utilisant la comparaison et les opérateurs logiques. Devinez combien de lignes elle renvoie :

SELECT spouse
  FROM simpsons
 WHERE      spouse = NULL
    OR NOT (spouse = NULL) 

Commençons par la première opération de comparaison :

WHERE      spouse = NULL

Quel que soit le contenu de la colonne de comparaison - salaires, noms d'animaux, etc. - si nous testons qu'elle est égale à NULL, le résultat est inconnu. Ceci est vrai même si la valeur de la colonne est NULL. C'est ce qui déroute les programmeurs qui ont l'expérience d'autres langages. Par exemple, en Python, la valeur None semble similaire à SQL NULL et peut être comparée à elle-même :

>>> None == None
True

Mais la première ligne de la clause WHERE va renvoyer unknown. Ainsi, lorsque notre requête est évaluée, elle ressemble à ceci :

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR NOT (spouse = NULL) 

Regardons la deuxième ligne de la condition WHERE :

   OR NOT (spouse = NULL)
	 

Cette comparaison va également renvoyer un résultat inconnu. Nous pouvons voir dans la table de vérité précédente que NOT unknown va renvoyer unknown. Donc maintenant notre requête est devenue :

SELECT spouse
  FROM simpsons
 WHERE unknown
    OR unknown 
	 

La table de vérité OR ci-dessus nous indique que le résultat de cette comparaison sera inconnu.

Une clause WHERE nécessite des conditions vraies. Le fait que le résultat ne soit pas faux n'est pas suffisant. Ainsi, même si la requête semble renvoyer toutes les lignes, la logique ternaire de SQL et la nature de SQL NULL font que zéro ligne est renvoyée.

Autres opérateurs de comparaison de SQL

Pour ces exemples, nous allons utiliser une table différente, pet_owners:

NULL et l'opérateur <

En utilisant la tablepet_owners , voyons qui a moins de 5 animaux de compagnie.

Le résultat :

Pourquoi ? Alice, qui n'a pas rempli son formulaire d'enquête sur les animaux domestiques, a NULL pour son pet_count. La valeur de tout NULL est inconnue. Est-ce que NULL < 5 ? C'est inconnu, donc Alice ne peut pas être incluse dans les résultats.

NULL et l'opérateur >

Maintenant, nous allons voir qui a plus de 3 animaux de compagnie.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count > 3
	 

Le résultat :

Pourquoi ? Encore une fois, c'est la valeur inconnue de NULL. Tout comme on ne sait pas si NULL 3 < 5, it is unknown if NULL >. Alice est exclue des résultats.

NULL et l'opérateur <=

Dans un changement subtil, nous obtiendrons maintenant une liste de tous ceux qui n'ont pas plus de 5 animaux de compagnie.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count <= 5
	 

Le résultat :

En remplaçant l'opérateur <= par <= dans notre première requête, nous ajoutons Bob à la liste des résultats, mais pas Alice. Dans la première requête, le nombre d'animaux de Bob (5) n'est pas inférieur à 5. Mais il est inférieur ou égal à 5, donc il est maintenant inclus dans le résultat de la requête. Alice n'apparaît toujours pas.

Lorsque nous examinons la ligne d'Alice, nous pouvons considérer que <= est un raccourci pour "NULL < 5 OU NULL = 5". Nous savons d'après ce qui précède que "NULL < ANYTHING" renverra un résultat inconnu; d'après ce qui précède, nous savons que "NULL = ANYTHING" renverra également un résultat inconnu. Une fois encore, Alice est exclue des résultats.

NULL et l'opérateur >=

Voyons maintenant qui a au moins 3 animaux de compagnie.

SELECT p.name
  FROM pet_owners p
 WHERE pet_count >= 3
	 

Le résultat :

Comme <=, nous pouvons considérer >= comme une combinaison logique de deux opérations de comparaison. Ainsi, pour Alice, la comparaison est équivalente à "NULL > 3 OU NULL = 3". Vous devriez maintenant savoir que cela ne peut aboutir qu'à une valeur inconnue.

Opérateurs de comparaison SQL qui fonctionnent avec les NULLs

Pour gérer correctement les NULL, SQL fournit deux opérateurs de comparaison spéciaux : IS NULL et IS NOT NULL. Ils ne renvoient que des valeurs vraies ou fausses et constituent la meilleure pratique pour intégrer des valeurs NULL dans vos requêtes.

Pour obtenir le résultat escompté, nous pouvons réécrire la requête Simpsons comme suit :

SELECT spouse
  FROM simpsons
 WHERE spouse IS NULL
    OR spouse IS NOT NULL
	 

Maintenant, la requête retournera chaque ligne, comme nous l'attendions.

SQL NULL et jointures externes

Il s'agit d'un sujet plus avancé. Si vous ne connaissez pas le site Les jointures en SQL, nous vous conseillons de commencer par lire 7 exemples de jointures SQL avec explications détaillées.

Il est courant d'utiliser une clause WHERE avec l'opérateur IS NOT NULL pour se débarrasser des lignes contenant des valeurs NULL. Mais cette méthode peut parfois s'avérer inefficace pour obtenir un résultat. Voici pourquoi.

Les jointures externes ( LEFT, RIGHT ou FULL JOIN ) peuvent être considérées comme une jointure INNER JOIN (qui renvoie les lignes correspondantes) plus les lignes non correspondantes dont les colonnes sont remplies de valeurs NULL.

Une LEFT JOIN renvoie toutes les lignes de la table de gauche de la jointure avec les lignes correspondantes de la table de droite (ou des valeurs NULL en l'absence de correspondance). Une RIGHT JOIN renvoie toutes les lignes de la table de droite avec les lignes correspondantes (ou les valeurs NULL) de la table de gauche. Une requête FULL JOIN ressemble à une requête INNER JOIN qui renvoie également toutes les lignes non correspondantes des tables de gauche et de droite prolongées par des valeurs NULL.

Si la clause WHERE de votre requête filtre les lignes qui ont été étendues par des NULL, vous annulez en fait votre jointure externe. Vous devez réécrire votre requête en utilisant une clause INNER JOIN.

Dans d'autres cas, les NULL interagissent avec votre clause WHERE et donnent des résultats incorrects. Examinons un exemple qui le démontre. Vous pouvez l'exécuter vous-même en utilisant ce Fiddle SQL(voici un guide d'autres sites à utiliser pour la pratique du SQL).

Pour cet exemple, nous allons utiliser deux tables, une pour customers et une pour orders.

Table customers

Table orders

Nous voulons voir le montant dépensé par tous nos clients enregistrés depuis le 4 février 2021 ("2021-02-04"). Nous commençons par un INNER JOIN. Il y a quelques fonctions SQL supplémentaires dans cette requête qui peuvent être nouvelles pour vous, mais LearnSQL vous couvre. Nous avons des articles sur COALESCE, les fonctions d'agrégation comme SUM, et la clause GROUP BY. Vous n'avez pas besoin de vous soucier de ce que font ces fonctions pour le moment. Concentrez-vous simplement sur le résultat de chaque requête, en commençant par celle-ci :

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
INNER JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

La requête renvoie ce résultat :

Cela semble bon, mais nous avons 3 clients. Si nous voulons voir tous les clients, nous devons utiliser LEFT OUTER JOIN (ou LEFT JOIN pour faire court). Cette requête inclura toutes les lignes de la table de gauche de l'instruction FROM, même s'il n'y a pas de données correspondantes dans la table de droite. Cela nous donne notre prochaine requête :

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Les résultats pourraient vous surprendre :

Pourquoi cela se produit-il ? Pourquoi Alice est-elle toujours absente ? Une requête plus simple nous donnera un indice :

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Le résultat :

Comme indiqué précédemment, LEFT JOIN inclut toutes les lignes de la table de gauche. Lorsqu'il n'y a pas de ligne correspondante dans la table de droite, les colonnes sont remplies de NULL.

La clause WHERE effectue son filtrage après JOIN, de sorte que toutes les lignes pour Alice seront supprimées parce que la comparaison de n'importe quoi à NULL, comme un order_date inexistant, renvoie un résultat inconnu. WHERE ne renvoie qu'une ligne où les conditionnels valent VRAI.

Pour résoudre ce problème, il faut déplacer l'expression conditionnelle, ici o.order_date > '2021-02-04', vers JOIN en l'incluant dans la clause ON:

SELECT c.name, COALESCE(SUM(o.amount),0) as 'Total'
FROM customers c 
LEFT JOIN orders o 
ON c.email = o.cust_email
WHERE o.order_date > '2021-02-04'
GROUP BY c.name;
	 

Comme nous utilisons un LEFT JOIN, Alice reste dans le jeu de résultats malgré la condition de date supplémentaire. Son NULL est transformé en un "0" plus propre par la fonction COALESCE(). Le résultat de la requête correspond maintenant à ce que nous attendions :

En savoir plus sur les NULL de SQL

Vous devriez maintenant comprendre comment SQL traite les valeurs NULL et les meilleures pratiques pour travailler avec elles. Vous connaissez les opérateurs IS NULL et IS NOT NULL et savez que la logique ternaire de SQL renvoie toujours un résultat inconnu lorsqu'un élément est comparé à un NULL , sauf dans deux cas particuliers. Vous avez également vu comment réécrire des requêtes pour ne pas avoir à filtrer ces NULL gênants.

Mais il y a encore beaucoup à apprendre sur l'utilisation des NULL en SQL. Je vous suggère de poursuivre votre apprentissage avec les articles Comment ORDER BY et NULL fonctionnent ensemble en SQL et Les valeurs NULL et la clause GROUP BY.

Et si vous voulez vraiment maîtriser SQL, je vous recommande la filière Exercices Pratiques de SQL ou la piste La pratique du SQL . Ils offrent une expérience d'apprentissage approfondie et facile à suivre qui vous aidera à affiner votre métier.