Comprendre la fonction COALESCE() en SQL

Dans un environnement technologique en constante évolution, il est primordial pour tout développeur de se tenir informé des dernières tendances. Que vous soyez novice ou expert, une bonne maîtrise de la manipulation des chaînes de caractères s'avère essentielle pour préparer les données (par exemple, générer un formulaire adapté à vos besoins spécifiques) et les gérer efficacement à l'aide des fonctions intégrées de SQL.

Au-delà de la simple manipulation, l'examen des jeux de données, l'évaluation des valeurs et l'encodage ou le décodage permettent d'obtenir des informations plus pertinentes. Cela facilite l'identification des valeurs manquantes, l'analyse de leur impact sur les calculs et l'optimisation du traitement des données, évitant ainsi les valeurs nulles qui peuvent fausser les résultats.

Ce guide explore la fonction `COALESCE` en SQL, un outil précieux pour la création de programmes sophistiqués. Il est destiné à ceux qui sont déjà familiers avec SQL et cherchent à approfondir leur compréhension de cette fonction spécifique. Notre série de guides SQL est également disponible pour vous aider à démarrer rapidement.

Qu'est-ce que `COALESCE()` en SQL et quelles sont ses applications ?

La fonction `COALESCE` en SQL examine une liste de paramètres (arguments) dans l'ordre et renvoie la première valeur non nulle rencontrée. En d'autres termes, elle parcourt séquentiellement votre liste jusqu'à ce qu'elle trouve la première valeur qui n'est pas nulle. Si tous les arguments sont nuls, la fonction retourne `NULL`.

Cette fonction est largement compatible et prise en charge par plusieurs systèmes de gestion de bases de données tels que MySQL, Azure SQL Database, Oracle et PostgreSQL.

Vous pouvez utiliser `COALESCE` dans les cas suivants :

Gestion efficace des valeurs `NULL`.
Exécution de plusieurs requêtes simultanément.
Éviter la complexité des instructions `CASE`.

Contrairement aux instructions `CASE` ou à la fonction `ISNULL`, `COALESCE` peut prendre un nombre variable de paramètres, ce qui permet d'écrire moins de code et simplifie la programmation.

Voici sa syntaxe :

COALESCE(valeur1, valeur2, valeur3, …, valeurX);

Dans SQL Server, `COALESCE` possède plusieurs caractéristiques : elle accepte des arguments de même type, un nombre variable de paramètres et peut être utilisée pour transformer des arguments de type entier en sorties entières.

À lire également : L'antisèche SQL ultime à conserver précieusement

Avant d'aborder l'utilisation concrète de `COALESCE`, penchons-nous sur la notion de `NULL`.

Qu'est-ce qu'une valeur `NULL` en SQL ?

En SQL, le marqueur `NULL` indique l'absence de valeur dans une base de données. Il représente une valeur indéfinie ou inconnue. Ne confondez pas `NULL` avec une chaîne vide ou une valeur nulle ; il signifie littéralement l'absence de valeur. L'apparition de `NULL` dans les colonnes d'une table signifie qu'une information est manquante.

Par exemple, dans une base de données d'un site de commerce en ligne, la colonne "identifiant client" peut contenir des valeurs `NULL` si le client n'a pas renseigné son identifiant. En SQL, `NULL` est un état spécifique, contrairement à d'autres langages où il peut signifier "ne pointe vers aucun objet particulier".

Les valeurs `NULL` ont un impact significatif sur les bases de données relationnelles. Elles permettent d'exclure certaines valeurs lors de l'utilisation de fonctions internes. Par exemple, lors du calcul du total des commandes dans un environnement de production, certaines commandes peuvent ne pas encore être complètes. En utilisant `NULL` comme espace réservé, la fonction `SUM` peut additionner correctement les totaux.

De même, lors du calcul de moyennes avec la fonction `AVG`, les valeurs `NULL` peuvent fausser les résultats. La base de données peut choisir de supprimer ces champs et d'utiliser `NULL`, ce qui permet d'obtenir des résultats plus précis.

Cependant, les valeurs `NULL` ne sont pas sans inconvénients. Elles sont considérées comme des valeurs de longueur variable, ce qui peut augmenter l'espace disque utilisé par la base de données, car celle-ci doit prévoir de l'espace pour stocker ces valeurs, même si elles dépassent la taille des données stockées normalement.

De plus, l'utilisation de certaines fonctions nécessite une personnalisation pour éliminer les valeurs `NULL`, ce qui allonge vos procédures SQL.

Gestion des valeurs `NULL` avec `COALESCE()`

Les valeurs nulles indiquent qu'une valeur pourrait exister, mais qu'elle n'est pas définie. Elles servent de substituts en attendant que des données réelles remplissent les champs.

Bien que vous puissiez utiliser `NULL` pour différents types de données, tels que les décimales, les chaînes, les blobs et les entiers, il est préférable de les éviter lors du traitement de données numériques, car elles peuvent nécessiter une clarification lors du développement du code.

Voici les principales façons d'utiliser `COALESCE()` pour gérer les valeurs `NULL` :

Remplacer les valeurs `NULL` par une valeur spécifique avec `COALESCE()`

Vous pouvez utiliser `COALESCE()` pour remplacer toutes les valeurs `NULL` par une valeur déterminée. Par exemple, une table "employés" avec une colonne "salaire" peut contenir des valeurs nulles si le salaire d'un employé n'a pas été crédité. Dans certains calculs, vous pouvez vouloir remplacer ces valeurs par zéro. Voici comment procéder.

SELECT COALESCE(salaire, 0) AS salaire_ajuste
FROM employés;

Sélectionner la première valeur non nulle parmi plusieurs options avec `COALESCE()`

Dans certains cas, vous pouvez vouloir utiliser la première valeur non `NULL` d'une liste d'expressions. Vous pouvez avoir plusieurs colonnes avec des données associées et vouloir donner la priorité à celles qui ne sont pas nulles. La syntaxe reste la même.

COALESCE (expression1, expression2, …)

Par exemple, imaginons que vous ayez une table de contacts avec les colonnes "nom_préféré" et "nom_complet". Vous souhaitez générer une liste de contacts avec, en priorité, leur nom préféré, ou à défaut, leur nom complet. Voici comment faire :

SELECT COALESCE(nom_préféré, nom_complet) AS nom_affichage
FROM contacts.

Si "nom_préféré" n'est pas `NULL`, il sera retourné. Sinon, le nom complet servira de nom d'affichage.

Concaténation de chaînes avec SQL `COALESCE`

La concaténation de chaînes en SQL peut poser problème si des valeurs `NULL` sont impliquées, car le résultat sera lui-même `NULL`. Pour éviter cela, vous pouvez utiliser la fonction `COALESCE`. Voici un exemple.

Une concaténation simple se présente ainsi :

SELECT 'Bonjour, où es-tu, '|| 'Jean '||'?' AS exemple

Le résultat est :

exempleBonjour, où es-tu, Jean ?

Cependant, si vous utilisez une valeur `NULL` :

SELECT 'Bonjour, où es-tu, ' || null || '?' AS exemple

La sortie devient :

`NULL`

Comme toute concaténation avec une valeur `NULL` donne `NULL`, le résultat ci-dessus est `NULL`. `COALESCE()` permet de résoudre ce problème en retournant une chaîne vide (ou un espace) au lieu de `NULL`. Par exemple, pour lister les noms de voitures avec leur fabricant :

SELECT 
voiture || ', fabricant: ' || COALESCE(fabricant, '—') AS marque_voiture
FROM stock

Si le fabricant est `NULL`, vous aurez '—' à la place. Voici le résultat attendu :

marque_voitureOutlander, fabricant : —Éperons volants, fabricant : BentleyAthlète royal, fabricant : —Saloon royal, fabricant : Crown

Les résultats `NULL` sont éliminés, et vous pouvez insérer une valeur de remplacement.

Fonction SQL `COALESCE` et pivotement

Le pivotement SQL transforme les lignes en colonnes. Il transpose les données d'une forme "normalisée" (beaucoup de lignes, peu de colonnes) vers une forme "dénormalisée" (moins de lignes, plus de colonnes). La fonction `COALESCE` permet de gérer les valeurs nulles dans les résultats pivotés.

Lors du pivotement, les colonnes résultantes sont des agrégats. Si un agrégat donne `NULL`, vous pouvez utiliser `COALESCE` pour remplacer `NULL` par une valeur par défaut. Par exemple :

Supposons une table "ventes" avec les colonnes "année", "trimestre" et "chiffre_affaires". Vous souhaitez pivoter les données pour avoir les années en colonnes et la somme des revenus de chaque trimestre en valeurs. Certains trimestres peuvent ne pas avoir de données, entraînant des valeurs `NULL`. Voici comment utiliser `COALESCE` pour les remplacer par zéro (0).

SELECT
    année,
    COALESCE(SUM(CASE WHEN trimestre="T1" THEN chiffre_affaires END), 0) AS Revenus_T1,
    COALESCE(SUM(CASE WHEN trimestre="T2" THEN chiffre_affaires END), 0) AS Revenus_T2,
    COALESCE(SUM(CASE WHEN trimestre="T3" THEN chiffre_affaires END), 0) AS Revenus_T3,
    COALESCE(SUM(CASE WHEN trimestre="T4" THEN chiffre_affaires END), 0) AS Revenus_T4
FROM ventes
GROUP BY année;

Fonction scalaire définie par l'utilisateur et fonction SQL `COALESCE`

Vous pouvez utiliser des fonctions scalaires définies par l'utilisateur (UDF) et `COALESCE` pour gérer les valeurs nulles dans des logiques complexes. Cela permet des transformations de données et des calculs avancés dans vos requêtes SQL. Par exemple, considérez une table "Employés" avec la structure suivante :

CREATE TABLE Employés (
    ID_Employé INT PRIMARY KEY,
    Prénom VARCHAR(50),
    Nom VARCHAR(50),
    Salaire INT,
    Prime INT
);

Vous voulez calculer les gains totaux de chaque employé (salaire + prime), mais certaines valeurs sont manquantes. Une UDF scalaire peut gérer les additions, tandis que `COALESCE` gère les valeurs nulles. Voici l'UDF pour les revenus totaux :

CREATE FUNCTION dbo.CalculerRevenusTotaux (@salaire INT, @prime INT)
RETURNS INT
AS
BEGIN
    DECLARE @revenusTotaux INT;
    SET @revenusTotaux = @salaire + COALESCE(@prime, 0);
    RETURN @revenusTotaux;
END;
Vous pouvez ensuite utiliser l'UDF scalaire avec COALESCE dans une requête :
SELECT ID_Employé, Prénom, Nom,
       Salaire, Prime, dbo.CalculerRevenusTotaux(Salaire, Prime) AS RevenusTotaux
FROM Employés;

Validation des données avec SQL `COALESCE`

Lors du travail avec des bases de données, il est important de valider les valeurs numériques. Par exemple, dans une table "produits" avec les colonnes "nom_produit", "prix" et "remise", vous voulez récupérer ces données, mais traiter les remises `NULL` comme étant égales à 0. `COALESCE` est une solution.

SELECT nom_produit, prix, COALESCE(remise, 0) AS remise 
FROM produits

`COALESCE` SQL et colonnes calculées

Les colonnes calculées sont des colonnes virtuelles dérivées d'expressions ou d'autres colonnes d'une table. Comme elles ne sont pas stockées physiquement, vous pouvez utiliser `COALESCE` pour gérer des scénarios et des transformations complexes. Par exemple :

Dans une table "produits" avec les colonnes "prix", "remise" et "taux_taxe", vous souhaitez créer une colonne calculée "prix_total" qui représente le prix final après application de la remise et de la taxe. Si la remise ou la taxe n'est pas spécifiée (`NULL`), vous voulez effectuer les calculs en utilisant 0. Voici comment utiliser `COALESCE` :

CREATE TABLE produits(
prix DECIMAL(10, 2),
remise DECIMAL(10, 2),
taux_taxe DECIMAL(5, 2),
prix_total AS (COALESCE(prix, 0) – COALESCE(prix*remise, 0))* COALESCE(1+taux_taxe, 1)
);

Ce code signifie que :

La colonne calculée `prix_total` est définie comme : (COALESCE(prix, 0) – COALESCE(prix*remise, 0))* COALESCE(1+taux_taxe, 1).

Si `prix` est `NULL`, `COALESCE(prix, 0)` s'assure qu'il est traité comme 0.

Si `remise` est `NULL`, `COALESCE(prix*remise, 0)` s'assure qu'elle est traitée comme 0, et la multiplication n'affecte pas le calcul.

Si `taux_taxe` est `NULL`, `COALESCE(1 + taux_taxe, 1)` garantit qu'il est traité comme 1, ce qui signifie qu'aucune taxe n'est appliquée et que la multiplication n'affecte pas le calcul.

Cette configuration permet de générer la colonne calculée `prix_total` avec le prix final réel, même en présence de valeurs manquantes ou `NULL`.

`COALESCE` SQL et expression `CASE`

Il est possible d'utiliser la syntaxe `COALESCE` via une expression `CASE`. Par exemple :

SELECT
NomProduit + ' '+ DateLivraison detailsProduit,
Fournisseur,
CASE
WHEN NumeroPortable is NOT NULL Then NumeroPortable
WHEN NumeroBureau is NOT NULL Then NumeroBureau
ELSE 'NA'
END
NumeroContactUrgence
FROM
dbo.tb_ContactUrgence

Ici, l'expression `CASE` fonctionne comme `COALESCE`.

De plus, il est possible d'utiliser `COALESCE` et `CASE` dans la même requête. Les deux techniques peuvent gérer les valeurs nulles et appliquer une logique conditionnelle en même temps. Prenons un exemple.

Dans une table "produits" avec les colonnes "id_produit", "nom_produit", "prix" et "remise", certains produits ont une remise, d'autres non. Vous voulez afficher le prix réduit si une remise existe, sinon le prix normal.

SELECT 
    id_produit,
    nom_produit,
    prix,
    COALESCE(
        CASE
            WHEN remise > 0 THEN prix - (prix * remise / 100)
            ELSE NULL
        END,
        prix
    ) AS prix_reduit
FROM produits;

Dans ce code, `CASE` vérifie si `remise` est supérieur à zéro et calcule le prix réduit, sinon il retourne `NULL`. La fonction `COALESCE` prend le résultat de `CASE` et `prix` comme paramètres. Elle retourne la première valeur non `NULL`, soit le prix réduit s'il existe, soit le prix normal dans le cas contraire.

Conclusion

Cet article a exploré différentes façons d'utiliser la fonction `COALESCE` dans vos requêtes de base de données. En évaluant les paramètres dans l'ordre et en retournant la première valeur non `NULL`, `COALESCE` simplifie vos requêtes et les rend plus efficaces.

`COALESCE` est une fonction polyvalente qui peut servir à gérer les valeurs nulles, concaténer des chaînes, pivoter des données, valider des données ou travailler avec des colonnes calculées. En maîtrisant la fonction `COALESCE`, les développeurs peuvent gérer les données manquantes et créer des bases de données sans erreur. La maîtrise de cette technique nécessite une pratique régulière.

Vous pouvez maintenant apprendre à créer des contraintes de clé étrangère en SQL.