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 de chaînes vous aide à préparer les données (par exemple, générer un formulaire différent de celui existant, le rendant utilisable pour votre entreprise) et à les gérer à l'aide des fonctions de serveur SQL intégrées.

Outre la manipulation des données, vous pouvez examiner des ensembles de données, évaluer des valeurs de données et les encoder ou les décoder pour générer des données plus significatives. Par conséquent, cela vous aide à parcourir les valeurs manquantes dans les ensembles de données, à comprendre leur impact sur les calculs et à rationaliser le travail global avec le processus de données pour éviter les valeurs Null qui peuvent ruiner les résultats des opérations.

Ce guide vous guide à travers la fonction coalesce dans SQL, qui aide à créer des programmes complexes. Le message suppose que vous avez rencontré et travaillé avec SQL et que vous cherchez simplement à renforcer votre compréhension de cette fonction particulière. Notre série de guides SQL peut vous aider à démarrer rapidement.

What is COALESCE () in SQL and Its Uses?

La fonction coalesce dans SQL évalue les paramètres (arguments) dans un ordre spécifié, comme des listes, et renvoie la première valeur non nulle. En termes simples, 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.

De plus, 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 lorsque :

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

Lorsqu'il est utilisé à la place des instructions CASE (ou de la fonction ISNULL), coalesce prend de nombreux paramètres, contrairement au 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(valueOne, valueTwo, valueThree, …, valueX);

Coalesce dans le serveur SQL a plusieurs propriétés, y compris des arguments du même type de données, acceptant de nombreux paramètres, et des arguments de type entier à cascader par une fonction de rendement pour renvoyer un entier en sortie.

A lire également: Feuille de triche SQL ultime à mettre en signet pour plus tard

Mais avant d'entrer dans l'utilisation de coalesce, comprenons NULL.

What is a NULL Value in SQL?

Le marqueur unique NULL en SQL indique l'inexistence d'une valeur dans la base de données. Vous pouvez le considérer comme une valeur indéfinie ou inconnue. S'il vous plaît, ne tombez pas dans le piège de le considérer comme une chaîne vide ou une valeur nulle ; c'est l'absence de valeur. L'occurrence nulle dans les colonnes du tableau représente des informations manquantes.

Dans un cas d'utilisation pratique, la colonne de données d'une colonne de base de données de site Web 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ù cela signifie "ne pointant pas vers un objet particulier" .

Les valeurs NULL en SQL ont un impact significatif sur les bases de données relationnelles. Premièrement, ils vous permettent d'exclure des valeurs particulières tout en travaillant avec d'autres fonctions internes. Par exemple, vous pouvez générer une liste des commandes totales dans un environnement de production, mais d'autres doivent encore être complétées. L'utilisation de NULL comme espace réservé permet à la fonction SOMME interne d'ajouter les totaux.

De plus, considérez 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. Au lieu de cela, la base de données peut supprimer ces champs et utiliser NULL, ce qui donne des sorties précises.

Les valeurs NULL n'ont pas d'inconvénients. Ils sont considérés comme des valeurs de longueur variable, qu'il s'agisse d'octets ou de 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 prend plus d'espace sur le disque dur au lieu d'utiliser des valeurs normales.

De plus, lorsque vous travaillez avec certaines fonctions, vous devrez les personnaliser pour éliminer les NULLS. Ceci, par conséquent, rend vos procédures SQL plus longues.

Handling NULL Values with COALESCE ()

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

Bien que vous puissiez utiliser des 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 recommandé 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'éclaircissements lorsque vous développerez le code qui fonctionne avec les données. Plus à ce sujet plus tard.

Les différentes manières dont COALESCE() peut être utilisé 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, lors de certains calculs, vous souhaiterez peut-être 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

Parfois, vous souhaiterez peut-être travailler avec les premières valeurs non NULL dans une liste d'expressions. Dans de tels cas, vous avez souvent plusieurs colonnes avec des données associées et vous souhaitez donner la priorité à leurs valeurs non NULL. La syntaxe demeure.

COALESCE (expression1, expression2, …)

Dans un cas pratique, supposons que vous disposiez d'un contacts tableau avec les colonnes preferred_name je pratique la randonnée full_name. Et vous voudriez générer une liste de contacts côte à côte avec leurs noms préférés (si disponibles) ou leurs noms complets. Voici comment y faire face.

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

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

Concaténation de chaînes avec SQL Coalesce

Vous pouvez rencontrer des problèmes avec SQL lorsque concaténation de chaînes si des valeurs nulles sont impliquées. Dans de tels 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. Ci-dessous un exemple.

Une simple concaténation de chaîne se fait par :

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

Le code renvoie :

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

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

La sortie est maintenant.

Étant donné que chaque concaténation de chaînes de texte impliquant une valeur NULL renvoie NULL, le résultat ci-dessus est NULL. Le problème est cependant résolu à l'aide de coalesce (). En utilisant cette fonction, vous renvoyez une chaîne vide (ou un espace) au lieu de NULL. Par exemple, supposons que vous répertoriez les 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 '–' au lieu 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 valeur de chaîne de remplacement.

Fonction SQL Coalesce et pivotement

Le pivotement SQL est une technique utilisée pour transformer des lignes en colonnes. Il vous permet de transposer (faire pivoter) les 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, transformer 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 donne null pour une cellule particulière, vous pouvez utiliser `SE FONDRE` pour remplacer les valeurs nulles par une valeur par défaut ou une représentation significative. Ci-dessous un exemple.

Prenons un tableau, sales, avec les colonnes year, quarteret au revenue, et vous voudriez faire pivoter les données ; de sorte que vous ayez des années comme colonnes et la somme des revenus de chaque trimestre comme valeurs. Mais, certains trimestres n'ont pas de données sur les revenus, 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 fusionner 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 un tableau, Employees, avec cette structure.

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

Vous voudrez peut-être calculer les gains totaux 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 bonus, tandis que coalesce gère les valeurs nulles. Voici l'UDF scalaire pour les revenus totaux.

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 avec SQL Coalesce

Lorsque vous travaillez avec des bases de données, vous souhaiterez peut-être valider des valeurs numériques. Par exemple, disons que vous avez les colonnes product_name, priceet au 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 de fusion peut être utile. Voici comment l'utiliser.

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

Coalesce SQL et colonnes calculées

Les colonnes calculées sont des colonnes virtuelles calculées en fonction d'expressions ou d'autres colonnes d'une table. Étant donné que les colonnes calculées ne sont pas physiquement stockées dans la base de données, vous pouvez les exploiter avec la fonction coalesce lors de la gestion de scénarios et de transformations complexes. Voici un exemple pratique de cas d'utilisation.

Considérez un `de produits` tableau avec les colonnes `prix', 'réduction`, et `taux d'imposition`. Dans ce cas, vous souhaitez créer une colonne calculée, `prix total`, 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 souhaitez poursuivre vos calculs en utilisant un zéro. Voici comment tirer parti de la fusion en fonction de 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 (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1).
2. Si la price est NUL, COALESCE(price*discount, 0) garantit qu'il est traité comme 0.
3. Si la discount est nul, COALESCE(price*discount) garantit qu'il est traité comme 0 et que la multiplication n'affecte pas le calcul.
4. Si la tax_rate est NUL, 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_price, une colonne calculée, avec le prix final réel, malgré les valeurs manquantes ou NULL.

Fusion SQL et expression CASE

Vous pouvez utiliser la syntaxe coalesce via l'expression CASE. 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.

De plus, en utilisant le COALESCE je pratique la randonnée CASE expressions dans la même requête est possible. Les deux techniques peuvent gérer des valeurs NULL et appliquer une logique conditionnelle simultanément. Illustrons cela par un exemple.

Considérez un cas où vous avez une table, products avec les colonnes product_id, product_name, priceet au 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 souhaitez 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, le `ÉTUDES` vérifie si le `réduction` est supérieur à zéro et calcule le prix réduit, sinon il renvoie une valeur NULL. Le `SE FONDRE` la fonction prend le résultat de `ÉTUDES` et `prix` comme paramètres. Il renvoie la première valeur non NULL, renvoyant effectivement le prix réduit s'il est disponible ou le prix normal s'il n'y en a pas.

Mot de la fin

Ce message a démontré diverses façons d'utiliser le `SE FONDRE` 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 en les rendant efficaces.

Coalesce est une fonction polyvalente, que vous gériez des valeurs nulles, la concaténation de chaînes, le pivotement des données, la validation ou que vous travailliez avec des colonnes calculées. En maîtrisant la fonction de fusion, les développeurs peuvent parcourir les données manquantes et créer des conceptions de base de données sans erreur. Rappelez-vous, pour maîtriser la technique; vous aurez peut-être besoin d'une pratique plus approfondie.

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