COALESCE (Transact-SQL)

项目
01/29/2025

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric 中的 SQL 终结点 Microsoft Fabric 中的仓库 Microsoft Fabric SQL 数据库

按顺序计算变量并返回最初不等于 NULL 的第一个表达式的当前值。下面的示例返回第三个值，因为第三个值是第一个不为 null 的值。

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

注意

如果要连接字符串，请改用 STRING_AGG。

Transact-SQL 语法约定

语法

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) 时，计算子查询两次。因此，您可能得到不同的结果，具体取决于查询的隔离级别。例如，在多用户环境中，代码在 NULL 隔离级别下可能返回 READ COMMITTED。要确保返回稳定的结果，请使用 SNAPSHOT ISOLATION 隔离级别，或使用 COALESCE 函数替换 ISNULL。此外，可以重写查询以将子查询推送到嵌套 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 返回值始终被视为 NOT 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 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 采用可变数量的参数。

示例

本文中的代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可以从 Microsoft SQL Server 示例和社区项目主页下载该数据库。

A. 从具有非 null 值的第一列返回数据

下面的示例演示 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中找到的非 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

通过

COALESCE (Transact-SQL)

语法

参数

expression

返回类型

备注

比较 COALESCE 和 CASE

比较 COALESCE 和 ISNULL

示例

A. 从具有非 null 值的第一列返回数据

B. 返回工资表中的非 null 值

反馈

其他资源

通过

COALESCE (Transact-SQL)

语法

参数

expression

返回类型

备注

比较 COALESCE 和 CASE

比较 COALESCE 和 ISNULL

示例

A. 从具有非 null 值的第一列返回数据

B. 返回工资表中的非 null 值

相关内容

反馈

其他资源