COALESCE (Transact-SQL)COALESCE (Transact-SQL)

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data Warehouse

Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. Например, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); возвращает третье значение, так как это первое значение, не равное NULL.For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

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

СинтаксисSyntax

COALESCE ( expression [ ,...n ] )   

АргументыArguments

expressionexpression
Выражение любого типа данных.Is an expression of any type.

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

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

Возвращает тип данных аргумента expression с наиболее высоким приоритетом.Returns the data type of expression with the highest data type precedence. Если ни одно из выражений не допускает значения NULL, то результат типизируется как не допускающий значения NULL.If all expressions are nonnullable, the result is typed as nonnullable.

RemarksRemarks

Если все аргументы имеют значение NULL, COALESCE возвращает NULL.If all arguments are NULL, COALESCE returns NULL. По крайней мере одно из значений NULL должно быть типизированным NULL.At least one of the null values must be a typed NULL.

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

Выражение COALESCE — синтаксический ярлык для выражения CASE.The COALESCE expression is a syntactic shortcut for the CASE expression. Это означает, что код COALESCE(expression1, ...n) переписывается оптимизатором запросов как следующее выражение CASE:That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE  
WHEN (expression1 IS NOT NULL) THEN expression1  
WHEN (expression2 IS NOT NULL) THEN expression2  
...  
ELSE expressionN  
END  

Поэтому входные значения (expression1, expression2, expressionN и т. д.) вычисляются многократно.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. Выражение значения, содержащее вложенный запрос, считается недетерминированным, и вложенный запрос вычисляется дважды.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. Этот результат соответствует стандарту SQL.This result is in compliance with the SQL standard. В любом случае могут быть возвращены различные результаты для первого и последующих вычислений.In either case, different results can be returned between the first evaluation and upcoming evaluations.

Например, если выполняется код COALESCE((subquery), 1), вложенный запрос вычисляется дважды.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. В результате можно получить различные результаты в зависимости от уровня изоляции запроса.As a result, you can get different results depending on the isolation level of the query. Например, код может вернуть NULL при уровне изоляции READ COMMITTED в многопользовательской среде.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Чтобы обеспечить устойчивые результаты, используйте уровень изоляции SNAPSHOT ISOLATION или замените COALESCE функцией ISNULL.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. Кроме того, можно переписать запрос, чтобы поместить вложенный запрос в подзапрос выборки, как показано в следующем примере:As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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 и ISNULLComparing COALESCE and ISNULL

Функция ISNULL и выражение COALESCE имеют аналогичные цели, но могут отличаться поведением.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Так как ISNULL — это функция, она вычисляется только один раз.Because ISNULL is a function, it's evaluated only once. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. Различается определение типа данных результирующего выражения.Data type determination of the resulting expression is different. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. Для ISNULL и COALESCE различается допустимость значения NULL для результирующего выражения.The NULLability of the result expression is different for ISNULL and COALESCE. Значение, возвращаемое ISNULL, никогда НЕ БЫВАЕТ нулевым (предполагается, что возвращаемое значение является ненулевым).The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). В то время как функция COALESCE с параметрами, которые не допускают значение NULL, считается имеющей значение NULL.By contrast,COALESCE with non-null parameters is considered to be NULL. Таким образом, выражения ISNULL(NULL, 1) и COALESCE(NULL, 1), несмотря на одинаковый синтаксис, имеют разные значения допустимости NULL.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. Эти выражения отличаются при использовании в вычисляемых столбцах, создании ограничений ключа или детерминировании возвращаемого значения определяемой пользователем скалярной функции для возможности индексации, как показано в приведенном ниже примере.These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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   
    );  
    
  4. Проверки для ISNULL и COALESCE также различаются.Validations for ISNULL and COALESCE are also different. Например, значение NULL для ISNULL преобразуется в значение int, а для COALESCE необходимо предоставить тип данных.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL принимает только два параметра.ISNULL takes only two parameters. А COALESCE принимает переменное количество параметров.By contrast COALESCE takes a variable number of parameters.

ПримерыExamples

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

В следующем примере показано, как функция COALESCE выбирает из первого столбца данные, отличные от значения NULL.The following example shows how COALESCE selects the data from the first column that has a nonnull value. В этом примере используется база данных AdventureWorks2012AdventureWorks2012.This example uses the AdventureWorks2012AdventureWorks2012 database.

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

Б.B. Выполнение сложного примераRunning a complex example

В следующем примере таблица wages включает три столбца с данными о ежегодной заработной плате сотрудников: hourly_wage, salary и commission.In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. Однако служащий получает только один тип выплат.However, an employee receives only one type of pay. Для определения общей оплаты для всех служащих используйте функцию COALESCE для получения не равных NULL значений столбцов hourly_wage, salary и commission.To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and 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  

Результирующий набор:Here is the result set.

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)

В. Простой примерC: Simple Example

В приведенном ниже примере показано, как COALESCE выбирает данные из первого столбца, содержащего значение, отличное от NULL.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. В этом примере предполагается, что таблица Products содержит следующие данные:Assume for this example that the Products table contains this data:

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

Затем выполняется следующий запрос COALESCE:We then run the following COALESCE query:

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

Результирующий набор:Here is the result set.

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

Обратите внимание на то, что в первой строке значение FirstNotNull равно PN1278, а не Socks, Mens.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. Этот параметр принимает такое значение, так как столбец Name в примере не был указан в качестве параметра для COALESCE.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

Г. Сложный примерD: Complex Example

В приведенном ниже примере COALESCE используется для сравнения значений в трех столбцах и возврата только значения, отличного от NULL, найденного в столбцах.The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

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;  

Результирующий набор:Here is the result set.

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

См. также:See Also

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