COALESCE (Transact-SQL)

Статья
01/07/2014

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

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (С первоначального выпуска по текущий выпуск).

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

Синтаксис

COALESCE ( expression [ ,...n ] )

Аргументы

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

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

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

Замечания

Если все аргументы равны NULL, функция COALESCE возвращает NULL. По меньшей мере одно из значений NULL должно быть типизированным NULL.

Сравнение COALESCE и CASE

Выражение COALESCE — это синтаксическое сокращение для выражения CASE. Код COALESCE(expression1,...n) переписывается оптимизатором запросов как следующее выражение CASE:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

Это значит, что входные значения (expression1, expression2, expressionN и т. д.) будут вычисляться много раз. Также в соответствии со стандартом SQL выражение значения, содержащее вложенный запрос, считается недетерминированным и вложенный запрос вычисляется дважды. В любом случае это может приводить к тому, что функция будет вычисляться дважды с возвратом различных результатов для каждого из вызовов.

Например, при выполнении кода COALESCE((subquery), 1) вложенный запрос выполняется дважды. В результате можно получить разные результаты в зависимости от уровня изоляции запроса. Например, код может вернуть NULL при уровне изоляции READ COMMITTED в многопользовательском окружении. Для получения стабильных результатов используйте уровень изоляции SNAPSHOT ISOLATION или замените COALESCE на функцию ISNULL. Аналогично можно переписать запрос на отправку вложенного запроса в подзапрос выборки, как показано в следующем примере.

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;

Сравнение COALESCE и ISNULL

Функция ISNULL и выражение COALESCE имеют одинаковое назначение, но разное поведение.

Так как ISNULL — функция, ее вычисление происходит только один раз. Как описано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.
Отличается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, а COALESCE следует правилам выражения CASE и возвращает тип данных значения с наибольшим приоритетом.
Возможность присвоения значения NULL результирующему выражению разнится для ISNULL и COALESCE. Возвращаемое значение ISNULL всегда считается недопускающим значений NULL, в то время как COALESCE с параметрами, отличными от NULL, рассматривается как NULL. Поэтому эквивалентные выражения ISNULL(NULL, 1) и COALESCE(NULL, 1) могут иметь различные значения, допускающие и не допускающие NULL. Это дает разницу при использовании данных выражений в вычисляемых столбцах, создании ограничений ключей или формировании детерминированного возвращаемого скалярного значения UDF для последующей индексации, как в следующем примере.
```
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 
);
```
Проверки для ISNULL и COALESCE также отличаются. Например, значение NULL для ISNULL конвертируется в int, а для COALESCE нужно указать тип данных.
ISNULL принимает только 2 параметра, а COALESCE принимает изменяемое число параметров.

Примеры

A.Выполнение простого примера

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

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

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

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)

См. также

Справочник

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

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

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