COALESCE (Transact-SQL)

Статья
05/23/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL. Например, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); возвращает третье значение, так как это первое значение, не равное NULL.

Соглашения о синтаксисе Transact-SQL

Синтаксис

COALESCE ( expression [ ,...n ] )

Аргументы

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

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

Возвращает тип данных аргумента 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 и возвращает тип данных значения с самым высоким приоритетом.
Для ISNULL и COALESCE различается допустимость значения NULL для результирующего выражения. Значение, возвращаемое ISNULL, никогда НЕ БЫВАЕТ нулевым (предполагается, что возвращаемое значение является ненулевым). В то время как функция COALESCE с параметрами, которые не допускают значение NULL, считается имеющей значение NULL. Таким образом, выражения ISNULL(NULL, 1) и COALESCE(NULL, 1), несмотря на одинаковый синтаксис, имеют разные значения допустимости NULL. Эти выражения отличаются при использовании в вычисляемых столбцах, создании ограничений ключа или детерминировании возвращаемого значения определяемой пользователем скалярной функции для возможности индексации, как показано в приведенном ниже примере.
```
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 принимает только два параметра. А COALESCE принимает переменное количество параметров.

Примеры

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

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

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

B. Выполнение сложного примера

В следующем примере таблица 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  
------------  
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)

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

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

Name         Color      ProductNumber  
------------ ---------- -------------  
Socks, Mens  NULL       PN1278  
Socks, Mens  Blue       PN1965  
NULL         White      PN9876

Затем выполняется следующий запрос COALESCE:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;

Результирующий набор:

Name         Color      ProductNumber  FirstNotNull  
------------ ---------- -------------  ------------  
Socks, Mens  NULL       PN1278         PN1278  
Socks, Mens  Blue       PN1965         Blue  
NULL         White      PN9876         White

Обратите внимание на то, что в первой строке значение FirstNotNull равно PN1278, а не Socks, Mens. Этот параметр принимает такое значение, так как столбец Name в примере не был указан в качестве параметра для COALESCE.

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

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

CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   NULL,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;

Результирующий набор:

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

См. также

ISNULL (Transact-SQL)
CASE (Transact-SQL)

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

COALESCE (Transact-SQL)

Синтаксис

Аргументы

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

Замечания

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

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

Примеры

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

B. Выполнение сложного примера

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

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

См. также

Обратная связь

Дополнительные ресурсы