COALESCE (Transact-SQL)

Статья
01/29/2025

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

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

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

Заметка

Если вы хотите объединить строки, используйте вместо этого STRING_AGG.

Соглашения о синтаксисе 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 всегда считается NOT NULLв состоянии (если возвращаемое значение равно 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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);
```
Проверки для ISNULL и COALESCE также различаются. Например, значение NULL для ISNULL преобразуется в значение int, а для COALESCE необходимо предоставить тип данных.
ISNULL принимает только два параметра. А COALESCE принимает переменное количество параметров.

Примеры

Примеры кода в этой статье используют пример базы данных AdventureWorks2022 или AdventureWorksDW2022, которую можно скачать на домашней странице примерах Microsoft SQL Server и проектах сообщества.

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

В приведенном ниже примере показано, как 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.

B. Возвращает значение, отличное от NULL, в таблице заработной платы

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

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

COALESCE (Transact-SQL)

Синтаксис

Аргументы

выражение

Возвращаемые типы

Замечания

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

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

Примеры

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

B. Возвращает значение, отличное от NULL, в таблице заработной платы

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

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

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

COALESCE (Transact-SQL)

Синтаксис

Аргументы

выражение

Возвращаемые типы

Замечания

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

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

Примеры

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

B. Возвращает значение, отличное от NULL, в таблице заработной платы

Связанное содержимое

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

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