Статья
09/28/2010

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
    );

Примеры

Простой пример

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

USE AdventureWorks ;

SELECT Name, Class, Color, ProductNumber,

COALESCE(Class, Color, ProductNumber) AS FirstNotNull

FROM Production.Product ;

Сложный пример

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

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

Общая зарплата

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

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)

См. также

Справочник

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

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

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