COALESCE (Transact-SQL)COALESCE (Transact-SQL)

S’APPLIQUE À : ouiSQL Server ouiAzure SQL Database ouiAzure Synapse Analytics (SQL DW) ouiParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Évalue les arguments dans l’ordre et retourne la valeur actuelle de la première expression qui ne prend pas initialement la valeur NULL.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. Par exemple, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); retourne la troisième valeur, car c’est la première valeur qui n’est pas Null.For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

COALESCE ( expression [ ,...n ] )   

ArgumentsArguments

expressionexpression
Expression de tout type.Is an expression of any type.

Types de retourReturn Types

Retourne le type de données de l’expression dont la priorité est la plus élevée.Returns the data type of expression with the highest data type precedence. Si aucune des expressions n'acceptent les valeurs NULL, le résultat est typé comme n'acceptant pas les valeurs NULL.If all expressions are nonnullable, the result is typed as nonnullable.

NotesRemarks

Si tous les arguments ont la valeur NULL, COALESCE retourne NULL.If all arguments are NULL, COALESCE returns NULL. Au moins une des valeurs Null doit être une valeur NULL typée.At least one of the null values must be a typed NULL.

Comparaison de COALESCE et de CASEComparing COALESCE and CASE

L’expression COALESCE est un raccourci syntaxique de l’expression CASE.The COALESCE expression is a syntactic shortcut for the CASE expression. Autrement dit, le code COALESCE(expression1, ...n) est réécrit par l’optimiseur de requête sous la forme de l’expression CASE suivante :That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

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.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. Une expression de valeur contenant une sous-requête est considérée comme non déterministe et la sous-requête est évaluée deux fois.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. Ce résultat est conforme à la norme SQL.This result is in compliance with the SQL standard. Dans l’un ou l’autre cas, les résultats retournés peuvent être différents entre la première évaluation et les suivantes.In either case, different results can be returned between the first evaluation and upcoming evaluations.

Par exemple, lorsque le code COALESCE((subquery), 1) est exécuté, la sous-requête est évaluée deux fois.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. Par conséquent, vous pouvez obtenir des résultats différents selon le niveau d'isolement de la requête.As a result, you can get different results depending on the isolation level of the query. Par exemple, le code peut retourner la valeur NULL avec le niveau d’isolement READ COMMITTED dans un environnement multi-utilisateurs.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Pour garantir des résultats stables, utilisez le niveau d’isolement SNAPSHOT ISOLATION ou remplacez COALESCE par la fonction ISNULL.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. 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 :As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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;  
  

Comparaison de COALESCE et de ISNULLComparing COALESCE and ISNULL

La fonction ISNULL et l’expression COALESCE ont un objectif similaire, mais peuvent se comporter différemment.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. ISNULL étant une fonction, elle est évaluée une seule fois.Because ISNULL is a function, it's evaluated only once. Comme décrit ci-dessus, les valeurs d’entrée pour l’expression COALESCE peuvent être évaluées plusieurs fois.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. La détermination du type de données de l'expression obtenue est différente.Data type determination of the resulting expression is different. ISNULL utilise le type de données du premier paramètre, COALESCE suit les règles de l’expression CASE et retourne le type de données de la valeur ayant la priorité la plus élevée.ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. La possibilité de valeurs Null de l’expression de résultat est différente pour ISNULL et COALESCE.The NULLability of the result expression is different for ISNULL and COALESCE. La valeur ISNULL renvoyée est toujours considérée comme n’acceptant PAS la valeur NULL (en supposant que la valeur renvoyée est non-NULL).The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). En revanche, la valeur COALESCE avec des paramètres non null est considérée comme NULL.By contrast,COALESCE with non-null parameters is considered to be NULL. Bien qu’elles soient égales, les expressions ISNULL(NULL, 1) et COALESCE(NULL, 1) ont des possibilités de valeur NULL différentes.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. Ces valeurs font une différence si vous utilisez ces expressions dans des colonnes calculées, si vous créez des contraintes de clé ou si vous rendez déterministe la valeur renvoyée par une fonction définie par l’utilisateur scalaire afin qu’elle puisse être indexée, comme le montre l’exemple suivant :These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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 integer 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 integer NULL,   
    col2 AS COALESCE(col1, 0),   
    col3 AS ISNULL(col1, 0) PRIMARY KEY   
    );  
    
  4. Les validations pour ISNULL et COALESCE sont également différentes.Validations for ISNULL and COALESCE are also different. Par exemple, la valeur NULL de ISNULL est convertie en type int, tandis que pour COALESCE, vous devez fournir un type de données.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL n’accepte que deux paramètres.ISNULL takes only two parameters. En revanche, COALESCE accepte un nombre variable de paramètres.By contrast COALESCE takes a variable number of parameters.

ExemplesExamples

A.A. Exécution d'un exemple simpleRunning a simple example

L'exemple suivant montre comment COALESCE sélectionne les données de la première colonne qui a une valeur non Null.The following example shows how COALESCE selects the data from the first column that has a nonnull value. Cet exemple utilise la base de données AdventureWorks2012AdventureWorks2012.This example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;  

B.B. Exécution d'un exemple complexeRunning a complex example

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.In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. Cependant, chaque employé ne perçoit qu'un seul type de salaire.However, an employee receives only one type of pay. Pour déterminer le montant total versé à tous les employés, utilisez COALESCE afin de recevoir seulement la valeur non NULL trouvée dans hourly_wage, salary et commission.To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and 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 l'ensemble des résultats.Here is the result set.

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  
  
(12 row(s) affected)

C : Exemple simpleC: Simple Example

L’exemple suivant montre comment COALESCE sélectionne les données de la première colonne qui a une valeur non-Null.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Cet exemple suppose que la table Products contient ces données :Assume for this example that the Products table contains this data:

Name         Color      ProductNumber  
------------ ---------- -------------  
Socks, Mens  NULL       PN1278  
Socks, Mens  Blue       PN1965  
NULL         White      PN9876

Nous exécutons ensuite la requête COALESCE suivante :We then run the following COALESCE query:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;  

Voici l'ensemble des résultats.Here is the result set.

Name         Color      ProductNumber  FirstNotNull  
------------ ---------- -------------  ------------  
Socks, Mens  NULL       PN1278         PN1278  
Socks, Mens  Blue       PN1965         Blue  
NULL         White      PN9876         White

Notez que dans la première ligne, la valeur FirstNotNull est PN1278, pas Socks, Mens.Notice that in the first row, the FirstNotNull value is PN1278, not 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.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D : Exemple complexeD: Complex Example

L’exemple suivant utilise COALESCE pour comparer les valeurs de trois colonnes et retourner uniquement la valeur non-Null trouvée dans les colonnes.The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

CREATE TABLE dbo.wages  
(  
    emp_id        tinyint   NULL,  
    hourly_wage   decimal   NULL,  
    salary        decimal   NULL,  
    commission    decimal   NULL,  
    num_sales     tinyint   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS decimal(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;  

Voici l'ensemble des résultats.Here is the result set.

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

Voir aussiSee Also

ISNULL (Transact-SQL) ISNULL (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)