即使第一个参数不为NULL，SQL Server也会读取所有COALESCE函数吗？

SELECT COALESCE(c.FirstName
                ,(SELECT TOP 1 b.FirstName
                  FROM TableA a 
                  JOIN TableB b ON .....)
                )

#1 楼

不。这是一个简单的测试：

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

如果第二个条件被求值，则会抛出一个除以零的异常。

MSDN文档，这与解释器如何查看COALESCE有关-这只是编写CASE语句的简便方法。

CASE是SQL Server中唯一（通常）可靠地短路的函数之一。

与标量变量和聚合进行比较时，有一些例外，例如由亚伦·伯特兰（Aaron Bertrand）给出的另一个答案（这将同时适用于CASE和COALESCE）：

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

将产生零误差除法。

这应该被认为是一个错误，通常，COALESCE将从左到右进行解析。

#2 楼

怎么样-正如伊兹克·本·甘（Itzik Ben-Gan）向我报告的，海梅·拉法格（Jaime Lafargue）告诉我的那样？

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

结果：

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

当然，有很简单的解决方法，但问题是CASE并不总是保证从左到右的评估/短路。我在这里报告了该错误，并将其关闭为“设计使然”。保罗·怀特（Paul White）随后提交了此Connect项目，并且已关闭为Fixed。不是因为它本身是固定的，而是因为他们用更准确的描述更新了联机丛书，在这种情况下，聚合可以更改CASE表达式的评估顺序。我最近在这里发布了更多有关此内容的信息。

编辑只是一个附录，尽管我同意这些都是极端情况，但在大多数情况下，您可以依靠从左到右的评估和短路，并且这些错误与文档相矛盾，并且最终可能会得到修复（这是不确定的-请参阅Bart Duncan的博客文章中的后续讨论，以了解原因），当人们说某些事情总是对的时，我必须不同意即使有一个单一的案例证明了这一点。如果Itzik和其他人可以找到这样的单独错误，那么至少在可能的范围内，还存在其他错误。而且由于我们不了解OP的其余查询，因此不能确定地说他会依靠这种短路，但最终会被其咬伤。所以对我来说，更安全的答案是：

尽管通常可以依靠CASE来评估左右和短路，如文档中所述，但说您不准确总是可以这样做。在此页面上有两种情况证明是不正确的，并且在任何SQL Server的公共可用版本中均未修复任何错误。

在这里进行编辑是另一种情况（我需要停止这样做），即使没有涉及聚合，CASE表达式也不会按照您期望的顺序求值。

#3 楼

该文档很清楚地表明了CASE的意图是短路。正如亚伦（Aaron）所提到的，已经有一些报道的实例表明事实并非总是如此。到目前为止，其中大多数已被确认为错误并已修复。

CASE（因此还有COALESCE）还有其他问题，其中使用了副作用函数或子查询。请考虑：

SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);

COALESCE表单通常返回null，如Hugo Kornelis的错误报告中所述。

优化程序转换的已演示问题和公共表达式跟踪表示不可能保证CASE在所有情况下都会短路。

我认为您可以有把握地确信CASE总体上会短路（特别是如果技术熟练的人员检查执行计划，并通过计划指南或提示“强制执行”执行计划），但是如果您需要绝对保证，则必须编写不包含任何表达式的SQL。

#4 楼

我遇到过另一种情况，其中CASE / COALESCE不会短路。如果将1作为参数传递，则以下TVF将引发PK违规。

CREATE FUNCTION F (@P INT)
RETURNS @T TABLE (
  C INT PRIMARY KEY)
AS
  BEGIN
      INSERT INTO @T
      VALUES      (1),
                  (@P)

      RETURN
  END

如果按如下方式调用

DECLARE @Number INT = 1

SELECT COALESCE(@Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = @Number), 
                         (SELECT TOP (1)  C
                          FROM   F(@Number))) 

或作为

DECLARE @Number INT = 1

SELECT CASE
         WHEN @Number = 1 THEN @Number
         ELSE (SELECT TOP (1) C
               FROM   F(@Number))
       END 

都给出结果


违反主键约束'PK__F__3BD019A800551192'。无法
在对象'dbo。@ T'中插入重复的密钥。重复的键值是
（1）。


表明SELECT（或至少是表变量填充）仍在执行并且即使该分支也产生错误决不能达到声明的目的。 COALESCE版本的计划如下。



重写查询似乎可以避免问题

SELECT COALESCE(Number, (SELECT number
                          FROM   master..spt_values
                          WHERE  type = 'P'
                                 AND number = Number), 
                         (SELECT TOP (1)  C
                          FROM   F(Number))) 
FROM (VALUES(1)) V(Number)   

哪个计划给出

#5 楼

另一个示例

CREATE TABLE T1 (C INT PRIMARY KEY)

CREATE TABLE T2 (C INT PRIMARY KEY)

INSERT INTO T1 
OUTPUT inserted.* INTO T2
VALUES (1),(2),(3);

查询

SET STATISTICS IO ON;

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (LOOP JOIN)

完全没有显示对T2的读取。

T2的查找处于通过谓词的条件下，并且运算符永远不会执行。但是

SELECT T1.C,
       COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C)  THEN -1 END)
FROM T1
OPTION (MERGE JOIN)

表明已读取T2。即使实际上根本不需要T2的值。

当然，这并不令人感到意外，但是我认为值得将其添加到示例存储库中，仅因为它引起了短路甚至在基于集合的声明性语言中意味着什么的问题。

#6 楼

我只是想提一个您可能没有考虑过的策略。这里可能不匹配，但有时确实派上用场。看看此修改是否可以为您提供更好的性能：

SELECT COALESCE(c.FirstName
            ,(SELECT TOP 1 b.FirstName
              FROM TableA a 
              JOIN TableB b ON .....
              WHERE C.FirstName IS NULL) -- this is the changed part
            )

另一种实现方法可能是这样（基本上是等效的，但允许您从其他查询访问更多列如果需要的话）：

SELECT COALESCE(c.FirstName, x.FirstName)
FROM
   TableC c
   OUTER APPLY (
      SELECT TOP 1 b.FirstName
      FROM
         TableA a 
         JOIN TableB b ON ...
      WHERE
         c.FirstName IS NULL -- the important part
   ) x

基本上，这是一种“硬”联接表的技术，但包括何时应联接任何行的条件。以我的经验，这有时确实有助于执行计划。

#7 楼

实际的标准说必须解析所有WHEN子句（以及ELSE子句）才能确定整个表达式的数据类型。我真的必须拿出一些旧笔记来确定如何处理错误。但是，就一开始，1/0使用整数，因此我认为这是一个错误。整数数据类型错误。当合并列表中仅包含空值时，确定数据类型会有些棘手，这是另一个问题。

#8 楼

不，不会。它只会在c.FirstName是NULL时运行。

但是，您应该自己尝试一下。实验。您说您的子查询很长。基准测试。对此得出自己的结论。


@Aaron对正在运行的子查询的回答更加完整。

但是，我仍然认为您应该重新编写查询并使用LEFT JOIN。在大多数情况下，可以通过将查询改写为使用LEFT JOIN来删除子查询。

使用子查询的问题是，由于子查询是针对每一行运行的，因此整体语句的运行速度会变慢在主查询的结果集中。