COALESCE (Transact-SQL)

项目
01/07/2014

按顺序计算参数，并返回最初没有计算为 NULL 的第一个表达式的当前值。

适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

语法

COALESCE ( expression [ ,...n ] )

参数

expression
是任何类型的表达式。

返回类型

返回数据类型优先级最高的 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) 时，将对子查询执行两次。因此，根据查询的具体隔离级别，您可能得到不同的结果。例如，该代码可在多用户环境中的 READ COMMITTED 隔离级别下返回 NULL。若要确保返回稳定结果，请使用 SNAPSHOT ISOLATION 隔离级别，或用 ISNULL 函数替换 COALESE。或者，可以重写查询以将子查询推送到嵌套 select 中，如下面的示例所示。

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 的值），而带有非 Null 参数的 COALESCE 被视为 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 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 的验证也是不同的。例如，ISNULL 的 NULL 值将会转换为 int，而对于 COALESCE，则必须提供数据类型。
ISNULL 仅采用两个参数，而 COALESCE 会采用数量可变的参数。

示例

A.运行简单示例

下面的示例演示 COALESCE 如何从第一个具有非 Null 值的列中选择数据。此示例使用 AdventureWorks2012 数据库。

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

B.运行复杂示例

在下面的示例中，wages 表包含三列，其中包含有关雇员年薪的信息：hourly wage、salary 和 commission。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的金额总数，请使用 COALESCE 仅接受在 hourly_wage、salary 和 commission 中找到的非 Null 值。

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)

请参阅

参考

ISNULL (Transact-SQL)

CASE (Transact-SQL)

通过