SQL Server - 神奇的CASE行为 - 简单CASE vs 搜索CASE

Question

我最近在 SQL Server (2014 如果有关系的话) 中遇到了一个问题，涉及到 CASE-THEN-ELSE 语句，更准确地说是 "简单" 和 "搜索" CASE 表达式。直到现在，我认为这两者之间唯一的区别只是表达式的格式和/或书写习惯，但我想我完全错了 :) MSDN 链接

CASE 表达式有两种格式：

简单 CASE 表达式将一个表达式与一组简单表达式进行比较以确定结果。

搜索 CASE 表达式评估一组布尔表达式以确定结果。

以下是示例：

set nocount on
declare @test nvarchar(50) = null

select 
    @test as [The NULL Value], 
    case 
       when @test is null 
          then null 
          else 'Not Null???' 
    end as [As Expected],
    case @test 
       when null 
          then null 
          else 'Not Null???' 
    end as [The Pickle] 

结果是：

The NULL Value                                     As Expected The Pickle
-------------------------------------------------- ----------- -----------
NULL                                               NULL        Not Null???

有人能提供一个MSDN文档的链接吗？其中可能更详细地解释了这个问题？ :)

P.S.：我敢打赌，你们很多人都认为两个结果会产生相同的输出:D

- Sergiu

---

请尝试在顶部使用 set ansi_nulls off 并观察会发生什么。;-) - shadow

Answer 1

非常正常...

"捷径"方法是...

 case @test 
       when null 
          then null 
          else 'Not Null???' 
    end as [The Pickle] 

评估变量/列（这里是 @test）与 WHEN 子句（when null）中的值使用常规等号运算符进行比较 - 并且使用标准等号运算符（@test = null）比较 NULL 总是未定义/NULL本身（标准 SQL 行为），因此它并不正确。

因此，您会对列 The Pickle 得到结果 Not Null???。

如果您想检查 NULL，您必须像在第一个示例中一样使用 IS NULL。

- marc_s

---

说得好。将 ansi_nulls 改为 off 就可以解决问题了。 - shadow

Answer 2

1. 请参考Transact-SQL参考中NULL和UNKNOWN的讨论，了解为什么'='不能用于NULL。
2. 简单CASE语句必须隐式使用'='，而不是IS NULL。因此，要使IS NULL明确，可以使用搜索CASE表达式。
3. 也许微软会在简单CASE表达式中添加一些功能，如果遇到NULL，则使用运算符'IS'？

- euUbsd36R

Answer 3

declare @t int =1

--simple case
select 
case @t 
when 1 then 1 else null end

上述查询被扩展为以下形式...

select 
case when @t=1 then 1 else null end

因此，带有 null 的查询将扩展为以下内容

declare @t int=null
select case @t
when null then null else 'notnull' end

被扩展为

select case when @t=null then null else 'notnull' end 

这显然会评估为非空值...

所以总结一下，只有在空值情况下，你才不会得到期望的结果，请尝试以下操作

declare @t int=null
declare @t1 int =1

select 
case when @t is null then null else 'notnull' end as 'Searchedcase',
case @t when null then null else 'notnull' end as'simple case',
case when @t1 =1 then 1 else  null end as 'Searchedcase for value',
case @t1 when 1 then 1 else null end as'simple case for value'

- TheGameiswar