Comment utiliser la fonction COALESCE() en SQL

Les utilisateurs de SQL sont souvent confrontés à des valeurs NULL dans leurs requêtes et doivent les traiter correctement. La fonction COALESCE() permet de gérer les valeurs NULL. Lisez cet article pour apprendre à utiliser COALESCE() dans vos requêtes.

Les tables SQL stockent les données dans des enregistrements, et les enregistrements sont composés de champs. Il peut arriver que nous ne connaissions pas la valeur d'un champ spécifique. Par exemple, supposons que nous ayons une table contenant les données suivantes persons. Elle contient les champs first_name, last_name et marital_status. Lorsque nous ne connaissons pas la valeur de marital_status pour une personne donnée, SQL nous permet d'attribuer une valeur NULL à ce champ. Cependant, la valeur NULL ne signifie pas que la personne n'a pas de marital_status; elle signifie seulement "nous ne connaissons pas cette valeur".

En d'autres termes, SQL utilise les valeurs NULL pour représenter l'absence de valeur. Cependant, les valeurs NULL peuvent être difficiles à gérer. C'est pourquoi SQL a inclus la fonction COALESCE(), dont nous allons parler dans cet article.

Avant d'aborder les détails techniques de NULL et COALESCE(), je vous suggère de consulter notre cours interactif sur Fonctions SQL standards. Il contient un examen complet des valeurs NULL, des fonctions liées à NULL et d'autres fonctions courantes en SQL.

Que fait COALESCE() ?

Dans les bases de données SQL, tout type de données admet NULL comme valeur valide ; c'est-à-dire que toute colonne peut avoir une valeur NULL, quel que soit son type de données. (Évidemment, certaines colonnes seront obligatoires (non nulles), mais cela est défini par le concepteur de la base de données, et non par le type de données lui-même). Montrons un exemple simple en utilisant la table persons.

Nous pouvons utiliser la fonction SQL COALESCE() pour remplacer la valeur NULL par un simple texte :

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status,'Unknown')
FROM persons

Dans la requête ci-dessus, la fonction COALESCE() est utilisée pour retourner la valeur 'Unknown' uniquement lorsque marital_status est NULL. Lorsque marital_status n'est pas NULL, COALESCE() renvoie la valeur de la colonne marital_status. En d'autres termes, COALESCE() renvoie le premier argument non NULL.

Découvrez l'exemple de données

Dans la suite de l'article, nous ferons la démonstration de la fonction COALESCE() à l'aide du tableau stockqui est illustrée ci-dessous.

Cette table stocke des enregistrements de données de produits pour un marché et comprend les colonnes product, brand, subcategory, category, family, units, quantity_available (le stock actuel de ce produit) et minimum_to_have (le seuil auquel le marché doit commander ce produit à ses fournisseurs).

Vous remarquerez que certains produits ont une sous-catégorie, mais d'autres non. Par exemple, le produit "côtes de porc" appartient à la sous-catégorie "viande de porc" dans la catégorie "viande" et la famille "alimentation". Le produit "Tomates" appartient à la catégorie "légumes" et à la famille "aliments" ; il n'a pas de sous-catégorie, il y a donc un NULL dans ce champ.

Exemples de requêtes SQL utilisant la fonction COALESCE()

Voyons maintenant comment utiliser la fonction COALESCE() dans quelques exemples réalistes.

Exemple 1 : Utiliser COALESCE() pour remplacer NULL par une étiquette

Nous voulons afficher tous les produits avec leur sous-catégorie, leur catégorie et leur famille. Cependant, certains produits ont un NULL dans leur catégorie ou sous-catégorie. Pour ces produits, nous voulons afficher un texte : 'No Category' ou 'No Subcategory'. Voici la requête que nous utiliserions :

SELECT product, 
  COALESCE(subcategory,'No Subcategory') AS subcategory,
  COALESCE(category,'No Category') AS category,
  COALESCE(family,'No Family') AS family
FROM stock

Nous utilisons la fonction COALESCE() pour remplacer les valeurs NULL par un texte. Vous pouvez voir le résultat ci-dessous :

Exemple 2 : Utiliser COALESCE() lors de la concaténation de NULL et de chaînes de caractères

Un problème SQL fréquent lié aux valeurs NULL est la concaténation de chaînes de caractères. De nombreuses opérations impliquant des valeurs NULL retournent un NULL comme résultat. Si nous voulons concaténer deux chaînes de caractères et que l'une d'entre elles est NULL, le résultat de la concaténation sera NULL. Voici une simple concaténation de texte :

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

Elle renvoie :

Cependant, si nous utilisons un NULL ...

SELECT 'Hello, how are you ' || null || '?'  AS example

... nous obtenons :

Le résultat est NULL car chaque concaténation de chaîne de texte impliquant une valeur NULL renvoie une valeur NULL. Pour éviter cela, nous pouvons utiliser la fonction COALESCE() pour renvoyer une chaîne vide (ou un espace) au lieu d'un NULL. Par exemple, supposons que nous voulions une liste des noms de produits avec le nom de la marque. Nous pouvons écrire la requête suivante :

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

Quand une marque est NULL, nous mettons un '--' au lieu d'un NULL. Remarquez le résultat :

Exemple 3 : Utiliser COALESCE() avec des arguments multiples

Vous pouvez utiliser la fonction COALESCE() avec plus de deux arguments. Supposons que nous voulions un rapport qui liste les produits et leurs sous-catégories. Si la sous-catégorie est NULL, nous voulons remplacer la sous-catégorie par la catégorie. Et si la sous-catégorie et la catégorie sont toutes deux NULL, nous voulons les remplacer par la famille du produit. Voyons la requête SQL :

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description ')
   AS product_and_subcategory
FROM stock

Nous utilisons la fonction COALESCE() avec quatre arguments ; le premier argument non NULL sera retourné, comme nous pouvons le voir dans le résultat ci-dessous :

Pour plus de détails à ce sujet, je vous suggère l'article Comment s'attaquer aux NULLs SQL.

Exemple 4 : Utiliser COALESCE() pour remplacer NULL par une valeur calculée

La fonction SQL COALESCE() peut également être utilisée pour calculer ou estimer une valeur lorsque cette valeur n'est pas présente. Par exemple, chaque produit a un seuil (représenté par la colonne minimum_to_have) qui nécessite une nouvelle commande auprès du fournisseur. Cependant, certains enregistrements peuvent avoir une valeur NULL dans la colonne minimum_to_have; dans ce cas, nous pouvons définir que le seuil sera de 50% de la colonne quantity_available. La requête pour calculer l'estimation du seuil est la suivante :

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

La fonction COALESCE() renvoie ici minimum_to_have lorsque la valeur minimum_to_have n'est pas NULL. Si minimum_to_have est NULL, alors COALESCE() retournera quantity_available * 0.5

Exemple 5 : Utiliser COALESCE() avec la clause ROLLUP

Dans l'exemple suivant, nous allons utiliser la clause ROLLUP (une extension de GROUP BY) pour obtenir la quantité totale de produits que nous avons pour chaque sous-catégorie, y compris un sous-total de produits pour chaque catégorie et famille. Voyons la requête :

SELECT family,
 category,
 subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

La clause ROLLUP suppose une hiérarchie entre les colonnes family, category, et subcategory. Ainsi, elle génère tous les ensembles de regroupement qui ont un sens compte tenu de la hiérarchie : GROUP BY family, GROUP BY family, category et GROUP BY family, category, subcategory. C'est la raison pour laquelle ROLLUP est souvent utilisé pour générer des sous-totaux et des totaux généraux pour les rapports.

Voyons les résultats ci-dessous :

Vous pouvez voir quelques NULLs dans le résultat précédent. Chaque NULL signifie que cette colonne n'était pas présente dans le GROUP BY pour le calcul de la quantité en stock. Par exemple, ces lignes ...

... sont le résultat de l'exécution de GROUP BY family, category. C'est la raison pour laquelle il y a un NULL sous la colonne subcategory.

Dans la prochaine requête, nous utiliserons la fonction COALESCE() pour améliorer la lisibilité du rapport. Nous remplacerons ces valeurs NULL par un texte clarifiant la raison de ce NULL :

SELECT COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

Le résultat est :

Dans le résultat précédent, nous pouvons observer comment les valeurs NULL sont remplacées par des textes et les lignes des rapports avec les textes commençant par 'All' montrent un sous-total dans quantity_in_stock.

Je vous suggère l'article La fonction SQL COALESCE : Handling NULL Values si vous souhaitez en savoir plus à ce sujet.

Tirer parti de COALESCE() pour traiter les valeurs NULL

Dans cet article, nous avons montré plusieurs façons d'utiliser la fonction SQL COALESCE(). Nous avons couvert comment utiliser COALESCE() pour remplacer les valeurs NULL, comment calculer une valeur alternative, et comment combiner COALESCE() avec la clause ROLLUP, entre autres exemples.

Avant de terminer, j'ai une autre recommandation à vous faire. Notre aide-mémoire gratuit Fonctions SQL standards vous permet de trouver rapidement des détails sur les fonctions intégrées de SQL, les fonctions d'agrégation, etc. Je l'utilise presque tous les jours lorsque je travaille avec SQL. Vous pouvez également essayer notre Fonctions SQL standards qui vous permet d'apprendre et de pratiquer les fonctions SQL numériques, textuelles, de date et de gestion des NULL. Améliorez vos compétences et augmentez vos atouts !