COALESCE (Transact-SQL)

项目
03/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 表达式规则并返回具有最高优先级的值的数据类型。
结果表达式是否可为 NULL 对于 ISNULL 和 COALESCE 是不同的。 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 只取 2 个参数而 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)

通过