Avec la croissance et l’évolution de la technologie, il est essentiel de se tenir au courant des dernières tendances en tant que développeur. Que vous soyez débutant ou expert, une solide compréhension de la manipulation des chaînes de caractères vous aide à préparer les données (par exemple, en générant un formulaire différent de celui qui existe déjà, afin de le rendre utilisable pour votre entreprise) et à les gérer à l’aide des fonctions intégrées du serveur SQL.

Outre la manipulation des données, vous pouvez examiner les ensembles de données, évaluer les valeurs des données et les encoder ou les décoder pour obtenir des données plus significatives. En conséquence, cela vous aide à naviguer à travers les valeurs manquantes dans les ensembles de données, à comprendre leur impact sur les calculs et à rationaliser l’ensemble du processus de travail avec les données pour éviter les valeurs nulles qui peuvent ruiner les résultats de l’opération.

Ce guide vous présente la fonction coalesce en SQL, qui permet de construire des programmes complexes. Il suppose que vous avez déjà utilisé SQL et que vous souhaitez renforcer votre compréhension de cette fonction particulière. Notre série de guides SQL peut vous aider à démarrer rapidement.

Qu’est-ce que COALESCE () en SQL et ses utilisations ?

La fonction coalesce en SQL évalue les paramètres (arguments) dans un ordre spécifié, comme des listes, et renvoie la première valeur non nulle. En d’autres termes, la fonction évalue votre liste de manière séquentielle et se termine à l’instance de la première valeur non nulle. Si tous les arguments de la liste sont nuls, la fonction renvoie NULL.

En outre, la fonction est inclusive et prise en charge dans d’autres bases de données telles que MYSQL, Azure SQL Database, Oracle et PostgreSQL.

Vous pouvez utiliser Coalesce dans les cas suivants :

  • Manipuler des valeurs NULL.
  • Exécution de plusieurs requêtes en une seule.
  • Éviter les instructions CASE longues et fastidieuses.

Lorsqu’il est utilisé à la place des instructions CASE (ou de la fonction ISNULL), Coalesce prend plusieurs paramètres, contrairement à CASE, qui n’en prend que deux. Cette approche vous permet d’écrire moins de code et facilite le processus d’écriture.

Voici la syntaxe :

COALESCE(valeurUn, valeurDeux, valeurTrois, ..., valeurX) ;

Coalesce dans le serveur SQL possède plusieurs propriétés, notamment des arguments du même type de données, l’acceptation de nombreux paramètres et des arguments de type entier à cascader par une fonction yield pour renvoyer un entier en sortie.

Lisez aussi : L’aide-mémoire SQL ultime à mettre en signet pour plus tard

Mais avant d’aborder l’utilisation de coalesce, nous allons comprendre ce qu’est NULL.

Qu’est-ce qu’une valeur NULL en SQL ?

Le marqueur unique NULL en SQL indique l’inexistence d’une valeur dans la base de données. Vous pouvez l’assimiler à une valeur indéfinie ou inconnue. Ne tombez pas dans le piège de penser qu’il s’agit d’une chaîne vide ou d’une valeur zéro ; il s’agit de l’absence d’une valeur. Les occurrences nulles dans les colonnes d’un tableau représentent des informations manquantes.

Dans un cas d’utilisation pratique, la colonne de données d’un site de commerce électronique peut être remplie par une valeur NULL si un client ne fournit pas son identifiant. La valeur NULL en SQL est unique ; il s’agit d’un état, contrairement à d’autres langages de programmation où elle signifie“ne pas pointer vers un objet particulier“.

Les valeurs NULL en SQL ont un impact significatif sur les bases de données relationnelles. Tout d’abord, elles vous permettent d’exclure des valeurs particulières lorsque vous travaillez avec d’autres fonctions internes. Par exemple, vous pouvez générer une liste de commandes totales dans un environnement de production, mais d’autres commandes doivent encore être complétées. L’utilisation de NULL comme valeur de remplacement permet à la fonction interne SUM d’ajouter les totaux.

En outre, envisagez les cas où vous devez générer la moyenne à l’aide de la fonction AVG. Si vous travaillez avec des valeurs nulles, les résultats sont faussés. La base de données peut supprimer ces champs et utiliser NULL, ce qui permet d’obtenir des résultats précis.

Les valeurs NULL ne présentent pas que des inconvénients. Elles sont considérées comme des valeurs de longueur variable, c’est-à-dire des octets ou plusieurs d’entre eux. Comme la base de données laisse de la place pour ces octets s’ils dépassent ce qui est stocké dans la base de données, il en résulte que votre base de données occupe plus d’espace sur le disque dur que si vous utilisiez des valeurs normales.

En outre, lorsque vous travaillez avec certaines fonctions, vous devez les personnaliser afin d’éliminer les NULLS. Par conséquent, vos procédures SQL sont plus longues.

Traitement des valeurs NULL avec COALESCE ()

Les valeurs nulles impliquent que vous pourriez avoir une valeur, mais que vous ne savez pas quelle devrait être cette valeur. Jusqu’à ce que vous recueilliez des données qui remplissent vos champs avec des valeurs réelles, les valeurs NULL sont les procurateurs.

Bien que vous puissiez utiliser les valeurs NULL pour plusieurs types de données dans votre base de données, y compris les décimales, les chaînes, les blobs et les entiers, il est préférable de les éviter lorsque vous traitez des données numériques.

L’inconvénient est que lorsqu’elles sont utilisées pour des valeurs numériques, vous aurez probablement besoin d’une clarification lorsque vous développerez le code qui fonctionne avec les données. Nous y reviendrons plus tard.

Les différentes façons d’utiliser COALESCE () pour gérer la valeur NULL :

Utilisation de COALESCE () pour remplacer les valeurs nulles par une valeur spécifique

Vous pouvez utiliser COALESCE () pour renvoyer des valeurs spécifiques pour toutes les valeurs nulles. Par exemple, vous pouvez avoir une table appelée “employés” avec une colonne “salaire”, qui peut contenir des valeurs nulles si le salaire des employés n’a pas été crédité. Ainsi, lorsque vous effectuez certains calculs, vous pouvez vouloir travailler avec une valeur spécifique, zéro dans ce cas, pour toutes les entrées NULL. Voici comment procéder.

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

Utilisation de COALESCE () pour sélectionner la première valeur non nulle parmi plusieurs options

Il peut arriver que vous souhaitiez utiliser les premières valeurs non nulles d’une liste d’expressions. Dans ce cas, vous disposez souvent de plusieurs colonnes contenant des données connexes et vous souhaitez donner la priorité à leurs valeurs non nulles. La syntaxe reste la même.

COALESCE (expression1, expression2, ...)

Dans un cas pratique, supposons 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 côte à côte avec leurs noms préférés (s’ils sont disponibles) ou leurs noms complets. Voici comment procéder.

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

Si le nom préféré n’est pas NULL pour ce cas de test, il sera renvoyé. Sinon, le nom complet est renvoyé comme nom d’affichage.

Concaténation de chaînes avec SQL Coalesce

Vous pouvez rencontrer des problèmes avec SQL lors de la concaténation de chaînes de caractères si des valeurs nulles sont impliquées. Dans ce cas, NULL est renvoyé comme résultat indésirable. Maintenant que NULL n’est pas le résultat souhaité, vous pouvez résoudre le problème à l’aide de la fonction coalesce. Voici un exemple.

Une simple concaténation de chaîne de caractères est réalisée par :

SELECT 'Bonjour, où êtes-vous, '|| 'Jean '|| ? AS exemple

Le code retourne :

Exemple
Bonjour, où êtes-vous, Jean ?

Cependant, si vous utilisez une valeur NULL, comme indiqué ci-dessous :

SELECT 'Bonjour, où êtes-vous, ' || null || '?' AS example

La sortie est maintenant.

Exemple
NULL

Étant donné que toute concaténation de chaîne de texte impliquant une valeur NULL renvoie NULL, le résultat ci-dessus est NULL. Le problème est cependant résolu en utilisant la fonction coalesce (). Cette fonction vous permet de renvoyer une chaîne vide (ou un espace) au lieu de NULL. Par exemple, supposons que vous établissiez la liste des noms de voitures et de leurs fabricants ; voici votre requête.

SELECT 
voiture || ', constructeur : ' || COALESCE(constructeur, '-') AS car_brand
FROM stock

Si le fabricant est NULL, vous obtiendrez ‘–‘ à la place de NULL. Voici les résultats attendus.

marque_voiture
outlander, constructeur : —
flying spurs, fabricant : Bentley
royal athlete, constructeur : —
royal saloon, fabricant : – Crown

Comme vous pouvez le voir, les résultats NULL sont éliminés, avec la possibilité d’insérer votre chaîne de remplacement.

Fonction SQL Coalesce et pivotement

Le pivotement SQL est une technique utilisée pour transformer des lignes en colonnes. Elle vous permet de transposer (faire pivoter) des données de la forme “normalisée” (avec beaucoup de lignes et moins de colonnes) à la forme “dénormalisée” (moins de lignes et plus de colonnes). La fonction coalesce peut être utilisée avec le pivot SQL pour gérer les valeurs nulles dans les résultats du pivot.

Lorsque vous effectuez un pivot en SQL, vous transformez les lignes en colonnes ; les colonnes résultantes sont des fonctions agrégées de certaines données. Si, dans un cas quelconque, une agrégation résulte en une valeur nulle pour une cellule particulière, vous pouvez utiliser `COALESCE` pour remplacer les valeurs nulles par une valeur par défaut ou une représentation significative. Voici un exemple.

Considérons un tableau, ventes, avec les colonnes année, trimestre et chiffre d'affaires, et vous voudriez faire pivoter les données ; de telle sorte que vous ayez les années comme colonnes et la somme du chiffre d’affaires pour chaque trimestre comme valeurs. Mais certains trimestres n’ont pas de données sur le chiffre d’affaires, ce qui donne des valeurs nulles dans le résultat pivoté. Dans ce cas, vous pouvez utiliser COALESCE pour remplacer les valeurs nulles dans le résultat pivoté par un zéro (0).

SELECT
    année,
    COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q3' THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q4' THEN revenue END), 0) AS Q4_Revenue
FROM ventes
GROUP BY year ;

Fonction scalaire définie par l’utilisateur et fonction SQL Coalesce

Vous pouvez utiliser les UDF scalaires et la fonction coalesce pour exécuter une logique complexe qui gère les valeurs nulles. La combinaison de ces fonctions vous aidera à réaliser des transformations de données et des calculs plus sophistiqués dans les requêtes SQL. Prenons l’exemple d’une table, Employés, dont la structure est la suivante.

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

Il se peut que vous souhaitiez calculer la rémunération totale de chaque employé (salaire plus prime). Cependant, il y a des valeurs manquantes. Dans ce cas, votre UDF scalaire peut gérer les ajouts de salaire et de prime, tandis que coalesce gère les valeurs nulles. Voici l’UDF scalaire pour le salaire total.

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT ;
    SET @totalEarnings = @salary COALESCE(@bonus, 0) ;
    RETURN @totalEarnings ;
FIN ;
Vous pouvez ensuite utiliser l'UDF scalaire avec coalesce dans une requête :
SELECT EmployeeID, FirstName, LastName,
       Salaire, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employés ;

Validation des données à l’aide de SQL Coalesce

Lorsque vous travaillez avec des bases de données, il se peut que vous souhaitiez valider des valeurs numériques. Par exemple, supposons que vous ayez les colonnes nom_produit, prix et remise dans une table, produits. Vous souhaitez récupérer les noms de produits, les prix et les remises de chaque article. Mais vous aimeriez traiter toutes les valeurs de remise NULL comme 0. La fonction coalesce peut vous être utile. Voici comment l’utiliser.

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

SQL Coalesce et colonnes calculées

Les colonnes calculées sont des colonnes virtuelles calculées sur la base d’expressions ou d’autres colonnes d’une table. Comme les colonnes calculées ne sont pas physiquement stockées dans la base de données, vous pouvez les utiliser avec la fonction coalesce lorsque vous traitez des scénarios et des transformations complexes. Voici un exemple de cas d’utilisation pratique.

Considérons une table `produits` avec les colonnes `prix`, `discount`, et `tax_rate`. Dans ce cas, vous voulez créer une colonne calculée, `total_price`, pour représenter le prix final du produit après application de la remise et de la taxe. Si la remise ou la taxe n’est pas spécifiée (NULL), vous voudrez poursuivre vos calculs en utilisant un zéro. Voici comment utiliser coalesce pour réaliser cette opération.

CREATE TABLE products(
prix DECIMAL(10, 2),
réduction DECIMAL(10, 2),
taux d'imposition DECIMAL(5, 2),
prix_total AS (COALESCE(prix, 0) - COALESCE(prix*remise, 0))* COALESCE(1 taux_d'imposition, 1)
) ;

Dans le code ci-dessus, voici ce qui se passe.

  1. La colonne calculée prix_total est définie comme (COALESCE(prix, 0) - COALESCE(prix*remise, 0))* COALESCE(1 taux_d'imposition, 1).
  2. Si le prix est NUL, COALESCE(prix*remise, 0) garantit qu’il est traité comme 0.
  3. Si la remise est nulle, COALESCE(prix*remise) s’assure qu’elle est traitée comme 0 et que la multiplication n’affecte pas le calcul.
  4. Si le taux d'imposition est NUL, COALESCE(1 taux d'imposition, 1) garantit qu’il est traité comme 0, ce qui signifie qu’aucune taxe n’est appliquée et que la multiplication n’affecte pas le calcul.

La configuration ci-dessus vous permet de générer le prix_total, une colonne calculée, avec le prix final réel, malgré des valeurs manquantes ou NULL.

Expression SQL Coalesce et CASE

Vous pouvez utiliser syntaxiquement coalesce par le biais de l’expression CASE. Voici un exemple :

SELECT
Nom du produit ' ' date de livraison productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE 'NA'
FIN
Numéro de contact d'urgence
FROM
dbo.tb_ContactUrgence

Dans la configuration ci-dessus, les requêtes CASE ressemblent à la fonction COALESCE.

En outre, il est possible d’utiliser les expressions COALESCE et CASE dans la même requête. Les deux techniques peuvent gérer les valeurs NULL et appliquer simultanément une logique conditionnelle. Illustrons cela par un exemple.

Prenons le cas d’une table de produits contenant les colonnes product_id, product_name, price et discount. Certains de vos produits bénéficient d’une remise spécifique, d’autres non. Si un produit bénéficie d’une réduction, vous souhaitez afficher le prix réduit, sinon, c’est le prix normal qui doit être affiché.

SELECT 
    produit_id,
    nom_du_produit,
    prix,
    COALESCE(
        CASE
            WHEN discount > 0 THEN prix - (prix * discount / 100)
            ELSE NULL
        FIN,
        prix
    ) AS discounted_price
FROM produits ;

Dans le code ci-dessus, la fonction `CASE` vérifie si la valeur de `discount` est supérieure à zéro et calcule le prix escompté, sinon elle renvoie la valeur NULL. La fonction `COALESCE` prend le résultat de `CASE` et `price` comme paramètres. Elle renvoie la première valeur non NULL, c’est-à-dire le prix réduit s’il est disponible ou le prix normal s’il n’y en a pas.

Quelques mots en guise de conclusion

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

Coalesce est une fonction polyvalente, qu’il s’agisse de gérer des valeurs nulles, de concaténer des chaînes de caractères, de pivoter des données, de valider des données ou de travailler avec des colonnes calculées. En maîtrisant la fonction coalesce, les développeurs peuvent naviguer à travers les données manquantes et créer des bases de données sans erreur. N’oubliez pas que pour maîtriser cette technique, vous devrez peut-être vous entraîner plus en profondeur.

Vous pouvez maintenant découvrir comment créer des contraintes de clé étrangère en SQL.