• Article

COALESCE (Transact-SQL)

Retourne la première expression non NULL parmi ses arguments.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

COALESCE ( expression [ ,...n ] )

Arguments

Types de retour

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.

[!REMARQUE]

Au moins une des valeurs Null doit être une valeur NULL typée.

COALESCE(expression1,...n) est équivalent à l'expression CASE suivante :

CASE

   WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

ISNULL et COALESCE, bien qu'équivalents, peuvent se comporter différemment. Une expression qui comprend ISNULL et des paramètres non NULL est considérée NOT NULL, tandis que les expressions comprenant COALESCE et des paramètres non NULL sont considérées NULL. Dans SQL Server, pour indexer des expressions comprenant COALESCE et des paramètres non Null, la colonne calculée peut être conservée à l'aide de l'attribut de colonne PERSISTED comme dans l'instruction suivante :

CREATE TABLE #CheckSumTest 
    (
        ID int identity ,
        Num int DEFAULT ( RAND() * 100 ) ,
        RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
    );

Exemples

Exemple simple

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

USE AdventureWorks ;

GO

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product ;

GO

Exemple complexe

Dans l'exemple suivant, la table wages comporte trois colonnes d'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.

Salaire total

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 ligne(s) affectée(s))