Comprendre la fonction COALESCE () en SQL

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 existant, ce qui le rend 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 afin d'éviter les valeurs nulles qui peuvent ruiner les résultats des opérations.

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 comment l'utiliser ?

La fonction coalesce de SQL évalue les paramètres (arguments) dans un ordre spécifié, comme les 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 incluse et prise en charge dans d'autres bases de données telles que MYSQLAzure SQL Database, Oracle et PostgreSQL.

Vous pouvez utiliser Coalesce dans les cas suivants :

• Traitement des valeurs NULL.
• Exécuter plusieurs requêtes en une seule.
• Éviter les déclarations 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 permet d'écrire moins de code et facilite le processus d'écriture.

Voici la syntaxe :

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

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.

A lire également : Ultimate SQL Cheat Sheet to Bookmark for Later

Mais avant de voir comment utiliser coalesce, il faut comprendre 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 nulle ; 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 pratique, la colonne de données d'un site de commerce électronique peut être remplie avec une valeur NULL si un client ne fournit pas son identifiant. Null en SQL est unique ; c'est un état, contrairement à d'autres langages de programmation où il 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 d'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, le résultat est 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.

Gestion 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 de vraies valeurs, les valeurs nulles sont les procurateurs.

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

L'inconvénient est que lorsqu'il est utilisé 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 dont COALESCE () peut être utilisé pour gérer la valeur NULL :

Utilisation de COALESCE () pour remplacer des 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(salary, 0) AS adjusted_salary
FROM employees;

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

Il peut arriver que vous souhaitiez travailler avec les premières valeurs non NULL d'une liste d'expressions. Dans ce cas, vous avez souvent plusieurs colonnes avec des données liées, et vous souhaitez donner la priorité à leurs valeurs non NULL. La syntaxe reste la même.

COALESCE (expression1, expression2, …)

Dans un cas pratique, supposons que vous ayez un contacts avec les colonnes preferred_name et full_name. 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(preferred_name, full_name) AS display_name
FROM contacts.

Si le preferred_name n'est pas NULL pour ce cas de test, il sera renvoyé. Dans le cas contraire, l'élément full-name est renvoyé comme nom d'affichage.

Concaténation de chaînes de caractères avec SQL Coalesce

Vous pouvez rencontrer des problèmes avec SQL lorsque 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 est réalisée par :

SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example

Le code revient :

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

SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

Le résultat est maintenant.

É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 permet de renvoyer une chaîne vide (ou un espace) au lieu de NULL. Par exemple, supposons que vous établissiez une liste de noms de voitures avec leurs fabricants ; voici votre requête.

SELECT 
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

Si le fabricant est NULL, vous aurez le '-' à la place de NULL. Voici les résultats attendus.

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 Pivot

Le pivotement SQL est une technique utilisée pour transformer des lignes en colonnes. Il 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 pivotés.

Lorsque vous PIVOT en SQL, transforment les lignes en colonnes ; les colonnes résultantes sont des fonctions agrégées de certaines données. Si, dans tous les cas, une agrégation aboutit à un résultat nul 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.

Prenons l'exemple d'une table, salesavec les colonnes year, quarteret revenueVous souhaitez faire pivoter les données de manière à ce que les années constituent des colonnes et que la somme des recettes pour chaque trimestre constitue les valeurs. Mais certains trimestres n'ont pas de données sur les recettes, 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
    year,
    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 sales
GROUP BY year;

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

Vous pouvez utiliser des UDF scalaires et coalescer pour exécuter une logique complexe qui gère les valeurs nulles. La combinaison de ces fonctionnalités 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, Employeesavec cette structure.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

Vous pourriez vouloir 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 la rémunération totale.

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;

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 disposiez des colonnes suivantes product_name, priceet discount dans un tableau, products. 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 être utile. Voici comment l'utiliser.

SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

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. Les colonnes calculées n'étant pas stockées physiquement dans la base de données, vous pouvez les exploiter avec la fonction coalesce lorsque vous traitez des scénarios et des transformations complexes. Voici un exemple de cas pratique.

Considérons un `products` tableau avec les colonnes `price`, `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), il est préférable de poursuivre les calculs en utilisant un zéro. Voici comment utiliser coalesce pour adapter l'opération.

CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

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

1. Les total_price La colonne calculée est définie comme suit (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1).
2. Si le price est NULL, COALESCE(price*discount, 0) garantit qu'il est traité comme 0.
3. Si le discount est nulle, COALESCE(price*discount) assure qu'il est traité comme 0 et que la multiplication n'affecte pas le calcul.
4. Si le tax_rate est NULL, COALESCE(1 + tax_rate, 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 total_priceune 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. En voici un exemple :

SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

Dans la configuration ci-dessus, CASE des requêtes comme la fonction COALESCE.

En outre, l'utilisation de la fonction COALESCE et CASE dans la même requête est possible. Les deux techniques peuvent gérer les valeurs NULL et appliquer une logique conditionnelle simultanément. Illustrons cela par un exemple.

Prenons le cas d'une table, products avec les colonnes product_id, product_name, priceet discount. Certains de vos produits bénéficient d'une remise spécifique, d'autres non. Si un produit bénéficie d'une remise, vous devez afficher le prix réduit, sinon le prix normal doit être affiché.

SELECT 
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

Dans le code ci-dessus, l'élément `CASE` vérifie si l'élément `discount` est supérieur à zéro, et calcule le prix actualisé, sinon il renvoie un NULL. La fonction `COALESCELa fonction ` prend le résultat de `CASE` et `price` comme paramètres. Il 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.

Dernières paroles

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

La fonction Coalesce est 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 parmi les données manquantes et créer des bases de données exemptes d'erreurs. N'oubliez pas que pour maîtriser la technique, vous devrez peut-être vous entraîner plus en profondeur.

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