СЛИЯНИЕ (Transact-SQL)

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

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

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

Note

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

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

Syntax

COALESCE ( expression [ , ...n ] )

Arguments

expression

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

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

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

Remarks

Если все аргументы имеют значение 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 значение всегда считается не допускаемым значением 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 принимает переменное количество параметров.

Examples

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

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

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