Статья
04/01/2012

COALESCE (Transact-SQL)

Возвращает первое выражение из списка аргументов, не равное NULL.

Значок ссылки на раздел Соглашение о синтаксисе в Transact-SQL

Синтаксис

COALESCE (expression [ ,
...n ] )

Аргументы

expression
Выражение любого типа данных.

Типы возвращаемых данных

Возвращает тип данных аргумента в выражении expression с наиболее высоким приоритетом. Если ни одно из выражений не допускает значения NULL, то результат типизируется как не допускающий значения NULL.

Замечания

Если все аргументы равны NULL, функция COALESCE возвращает NULL.

Примечание
По меньшей мере одно из значений NULL должно быть типизированным NULL.

Функция COALESCE (expression1,...n) является эквивалентом следующего выражения CASE:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

Поведение функций ISNULL и COALESCE может быть различным, несмотря на их равнозначность. Выражение, содержащее ISNULL с параметрами, отличными от NULL, считается NOT NULL, в то время как выражения, содержащие COALESCE с параметрами, отличными от NULL, рассматриваются как NULL. В SQL Server, для индексации выражений, содержащих COALESCE с параметрами, отличными от NULL, вычисляемый столбец можно сохранить с помощью атрибута столбца PERSISTED, как в следующей инструкции:

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

Примеры

A. Запуск простого примера

В следующем примере показано, как функция COALESCE выбирает из первого столбца данные, отличные от значения NULL.

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

Б. Запуск сложного примера

В следующем примере таблица wages включает три столбца с данными о ежегодной заработной плате сотрудников: hourly_wage, salary и commission. Однако служащий получает только один тип выплат. Для определения общей оплаты для всех служащих используйте функцию COALESCE для получения не равных NULL значений столбцов hourly_wage, salary и 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

Ниже приводится результирующий набор.

Total Salary

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

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

См. также

Справочник

Функция ISNULL (Transact-SQL)

Выражение CASE (Transact-SQL)

Поделиться через