COALESCE (Transact-SQL)
S’applique à :
SQL ServerBase de données Azure SQLAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison d'analyse SQL dans Microsoft FabricEntrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric
Évalue les arguments dans l’ordre et retourne la valeur actuelle de la première expression qui ne prend pas initialement la valeur NULL. L’exemple suivant retourne la troisième valeur, car la troisième valeur est la première valeur qui n’est pas null.
SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');
Note
Si vous souhaitez concaténer des chaînes, utilisez STRING_AGG à la place.
Conventions de la syntaxe Transact-SQL
Syntaxe
COALESCE ( expression [ , ...n ] )
Arguments
expression
Expression de n’importe quel type.
Types de retour
Retourne le type de données de l’expression dont la priorité est la plus élevée. Si toutes les expressions ne sont pas nullables, le résultat est typé comme non nullable.
Notes
Si tous les arguments ont la valeur NULL, COALESCE retourne NULL. Au moins une des valeurs Null doit être une valeur NULL typée.
Comparer COALESCE et CASE
L’expression COALESCE est un raccourci syntaxique de l’expression CASE. Autrement dit, le code COALESCE(<expression1>, ...n) est réécrit par l’optimiseur de requête comme expression CASE suivante :
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
Cela signifie que les valeurs d’entrée (expression1, expression2, expressionN, etc.) sont évaluées plusieurs fois. Une expression de valeur qui contient une sous-requête est considérée comme non déterministe et la sous-requête est évaluée deux fois. Ce résultat est conforme à la norme SQL. Dans l’un ou l’autre cas, les résultats retournés peuvent être différents entre la première évaluation et les suivantes.
Par exemple, lorsque le code COALESCE((subquery), 1) est exécuté, la sous-requête est évaluée deux fois. Par conséquent, vous pouvez obtenir des résultats différents selon le niveau d'isolement de la requête. Par exemple, le code peut retourner la valeur NULL avec le niveau d’isolement READ COMMITTED dans un environnement multi-utilisateurs. Pour garantir des résultats stables, utilisez le niveau d’isolement SNAPSHOT ISOLATION ou remplacez COALESCE par la fonction ISNULL. Vous pouvez également réécrire la requête pour envoyer (push) la sous-requête dans une sous-sélection, comme le montre l’exemple suivant :
SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
FROM (SELECT (SELECT Nullable
FROM Demo
WHERE SomeCol = 1) AS x) AS T;
Comparer COALESCE et ISNULL
La fonction ISNULL et l’expression COALESCE ont un objectif similaire, mais peuvent se comporter différemment.
ISNULLétant une fonction, elle est évaluée une seule fois. Comme décrit précédemment, les valeurs d’entrée de l’expressionCOALESCEpeuvent être évaluées plusieurs fois.La détermination du type de données de l'expression obtenue est différente.
ISNULLutilise le type de données du premier paramètre etCOALESCEsuit les règles d’expressionCASEpour retourner le type de données de valeur avec la priorité la plus élevée.La possibilité de valeurs Null de l’expression de résultat est différente pour
ISNULLetCOALESCE. La valeur de retourISNULLest toujours considérée commeNOT NULLen mesure (en supposant que la valeur de retour est une valeur non nullable). En revanche, la valeurCOALESCEavec des paramètres non null est considérée commeNULL. Bien qu’elles soient égales, les expressionsISNULL(NULL, 1)etCOALESCE(NULL, 1)ont des possibilités de valeur NULL différentes. Ces valeurs font une différence si vous utilisez ces expressions dans des colonnes calculées, en créant des contraintes de clé ou en faisant la valeur de retour d’une fonction scalaire définie par l’utilisateur (UDF), afin qu’elle puisse être indexée comme indiqué dans l’exemple suivant :USE tempdb; GO -- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL. CREATE TABLE #Demo ( col1 INT NULL, col2 AS COALESCE (col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) ); -- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL. CREATE TABLE #Demo ( col1 INT NULL, col2 AS COALESCE (col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY );Les validations pour
ISNULLetCOALESCEsont également différentes. Par exemple, la valeurNULLdeISNULLest convertie en type int, tandis que pourCOALESCE, vous devez fournir un type de données.ISNULLn’accepte que deux paramètres. En revanche,COALESCEaccepte un nombre variable de paramètres.
Exemples
Les exemples de code de cet article utilisent l’exemple de base de données AdventureWorks2022 ou AdventureWorksDW2022, que vous pouvez télécharger à partir de la page d’accueil Microsoft SQL Server Samples and Community Projects.
R. Retourner des données de la première colonne qui a une valeur non Null
L’exemple suivant montre comment COALESCE sélectionne les données de la première colonne qui a une valeur non-Null. Cet exemple suppose que la table Products contient ces données :
Name Color ProductNumber
------------ ---------- -------------
Socks, Mens NULL PN1278
Socks, Mens Blue PN1965
NULL White PN9876
Nous exécutons ensuite la requête COALESCE suivante :
SELECT Name,
Color,
ProductNumber,
COALESCE (Color, ProductNumber) AS FirstNotNull
FROM Products;
Voici le jeu de résultats.
Name Color ProductNumber FirstNotNull
------------ ---------- ------------- ------------
Socks, Mens NULL PN1278 PN1278
Socks, Mens Blue PN1965 Blue
NULL White PN9876 White
Dans la première ligne, la valeur FirstNotNull est PN1278, et non Socks, Mens. Cette valeur est celle-ci car la colonne Name n’a pas été spécifiée en tant que paramètre de COALESCE dans l’exemple.
B. Retourner la valeur non null dans une table de salaires
Dans l'exemple suivant, la table wages comporte trois colonnes qui contiennent des informations sur les salaires annuels des employés : salaire horaire, salaire et commission. Cependant, chaque employé ne perçoit qu'un seul type de salaire. Pour déterminer le montant total payé à tous les employés, utilisez COALESCE pour recevoir uniquement la valeur non null trouvée dans hourly_wage, salaryet commission.
SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
emp_id TINYINT IDENTITY,
hourly_wage DECIMAL NULL,
salary DECIMAL NULL,
commission DECIMAL NULL,
num_sales TINYINT NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES (10.00, NULL, NULL, NULL),
(20.00, NULL, NULL, NULL),
(30.00, NULL, NULL, NULL),
(40.00, NULL, NULL, NULL),
(NULL, 10000.00, NULL, NULL),
(NULL, 20000.00, NULL, NULL),
(NULL, 30000.00, NULL, NULL),
(NULL, 40000.00, NULL, NULL),
(NULL, NULL, 15000, 3),
(NULL, NULL, 25000, 2),
(NULL, NULL, 20000, 6),
(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST (COALESCE (hourly_wage * 40 * 52, salary, commission * num_sales) AS MONEY) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO
Voici le jeu de résultats.
Total Salary
------------
10000.00
20000.00
20800.00
30000.00
40000.00
41600.00
45000.00
50000.00
56000.00
62400.00
83200.00
120000.00