COALESCE (Transact-SQL)
Область применения: 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 ] )
Аргументы
выражение
Выражение любого типа данных.
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Типы возвращаемых данных
Возвращает тип данных аргумента 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