COALESCE (Transact-SQL)

Évalue les arguments dans l'ordre et retourne la valeur actuelle de la première expression qui ne prend pas initialement la valeur NULL.

Conventions de la syntaxe Transact-SQL

Syntaxe

COALESCE ( expression [ ,...n ] ) 

Arguments

• expression
  Expression de tout type.

Types des valeurs retournées

Retourne le type de données de expression dont la priorité de type de données est la plus élevée. Si aucune des expressions n'acceptent les valeurs NULL, le résultat est typé comme n'acceptant pas les valeurs NULL.

Notes

Si tous les arguments sont NULL, COALESCE retourne la valeur NULL. Au moins une des valeurs Null doit être une valeur NULL typée.

Comparaison de COALESCE et de 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. En outre, conformément à la norme SQL, 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. Dans l'un et l'autre cas, différents résultats peuvent être retournés 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 COALESE par la fonction ISNULL. Vous pouvez éventuellement réécrire la requête pour envoyer 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;

Comparaison de COALESCE et de ISNULL

La fonction ISNULL et l'expression COALESCE ont un objectif similaire, mais peuvent se comporter différemment.

1. ISNULL étant une fonction, elle est évaluée une seule fois. Comme décrit ci-dessus, les valeurs d'entrée pour l'expression COALESCE peuvent être évaluées plusieurs fois.

2. La détermination du type de données de l'expression obtenue est différente. 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.

3. La possibilité de valeurs nulles de l'expression de résultat est différente pour ISNULL et COALESCE. La valeur de retour ISNULL est toujours considérée comme n'acceptant pas la valeur NULL (en supposant que la valeur de retour est non NULL), tandis que COALESCE avec des paramètres non NULL est considéré comme NULL. Les expressions ISNULL(NULL, 1) et COALESCE(NULL, 1) ont ainsi des possibilités de valeur NULL différentes bien qu'elles soient équivalentes. Cela a son importance si vous utilisez ces expressions dans des colonnes calculées, créez des contraintes de clé ou rendez déterministe la valeur de retour d'une fonction définie par l'utilisateur scalaire afin qu'elle puisse être indexée, comme le montre 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 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 de ISNULL et COALESCE sont également différentes. Par exemple, une valeur NULL pour ISNULL est convertie en int alors que pour COALESCE, vous devez fournir un type de données.

5. ISNULL accepte uniquement 2 paramètres, tandis que COALESCE accepte un nombre variable de paramètres.

Exemples

A.Exécution d'un exemple simple

L'exemple suivant montre comment COALESCE sélectionne les données de la première colonne qui a une valeur non Null. Cet exemple utilise la base de données AdventureWorks2012.

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

B.Exécution d'un exemple complexe

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 versé à tous les employés, utilisez COALESCE afin de recevoir seulement la valeur non NULL trouvée dans hourly_wage, salary et 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.

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)

Voir aussi

Référence

ISNULL (Transact-SQL)

CASE (Transact-SQL)