Sql Server：CASE 语句与 NULL 比较时出现意外行为

发布于 2024-12-02 05:43:32 字数 203 浏览 1 评论 0原文

给定：

以下 Select 语句：

select case NULL
       when NULL then 0
       else 1
       end

问题：

我期望它返回 0，但它却返回 1。结果是什么？

原文

Given:

The following Select statement:

select case NULL
       when NULL then 0
       else 1
       end

Problem:

I'm expecting this to return 0 but instead it returns 1. What gives?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦幻的味道 2024-12-09 05:43:32

一般来说，您不应该尝试比较 NULL 是否相等，而 case 语句就是这样做的。您可以使用“Is NULL”来测试它。不存在 NULL != NULL 或 NULL = NULL 的期望。它是一个不确定的、未定义的值，而不是一个硬常量。

-- 为了包含评论中的问题 --

如果您需要在可能遇到 NULL 列时检索值，请尝试这样做：

Case
  When SomeColumn IS NULL
  Then 0
  Else 1
End

我相信这应该可行。就您的原始帖子而言：

Select Case NULL
  When NULL then 0 // Checks for NULL = NULL
  else 1 // NULL = NULL is not true (technically, undefined), else happens
end

问题是您的案例选择会自动尝试使用相等操作。这对于 NULL 来说根本不起作用。

Generally speaking, NULL is not something you should attempt to compare for equality, which is what a case statement does. You can use "Is NULL" to test for it. There is no expectation that NULL != NULL or that NULL = NULL. It's an indeterminate, undefined value, not a hard constant.

-- To encompass questions in the comments --

If you need to retrieve a value when you may encounter a NULL column, try this instead:

Case
  When SomeColumn IS NULL
  Then 0
  Else 1
End

I believe that should work. As far as your original post is concerned:

Select Case NULL
  When NULL then 0 // Checks for NULL = NULL
  else 1 // NULL = NULL is not true (technically, undefined), else happens
end

The trouble is that your Case select automatically attempts to use equality operations. That simply doesn't work with NULL.

回复收藏 0 原文

中性美 2024-12-09 05:43:32

我本来打算将其添加为亚伦答案的评论，但它太长了，所以我将其添加为另一个（部分）答案。

CASE 语句实际上有两种不同的模式，简单，搜索。

来自博尔：

CASE 表达式有两种格式：
简单的 CASE 表达式将一个表达式与一组简单表达式进行比较以确定结果。
搜索到的 CASE 表达式会计算一组布尔表达式来确定结果。

当简单的CASE（您的示例）执行其所描述的比较时，它会执行相等比较 - 即=

这在后面的文档中得到了澄清：

简单的 CASE 表达式通过比较第一个表达式来运算
每个 WHEN 子句中的表达式的等效性。如果这些
表达式是等价的，THEN 子句中的表达式将是
返回。
仅允许相等性检查。

因为 anything = NULL 在 ANSI SQL 中始终为 false（如果您不知道这一点，则需要更广泛地阅读 SQL 中的 NULL，特别是其他搜索比较中的行为 - WHERE x IN (a, b, c))，您不能在简单情况下使用NULL并将其与值进行比较，带有 NULL 要么初始表达式或要比较的表达式列表中的值。

如果要检查 NULL，则必须使用 IF/ELSE 构造或带有完整表达式的搜索 CASE。

我同意，不幸的是没有版本支持 IS 比较以使其更容易编写：

select case colname
   when IS NULL then 0
   else 1
end

这将使编写某些长 CASE 语句更容易：

select case colname
   when IS NULL then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

但这只是一厢情愿的想法......

一个选项是使用 ISNULL 或 COALESCE：

select case COALESCE(colname, 999999) -- 999999 is some value never used
   when 999999 then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

但这并不总是一个好的选择。

I was going to add this as a comment to Aaron's answer, but it was getting too long, so I'll add it as another (part of the) answer.

The CASE statement actually has two distinct modes, simple and searched.

From BOL:

The CASE expression has two formats:
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.

When the simple CASE (your example) does what it describes as comparison it does an equality comparison - i.e. =

This is clarified in the later documentation:

The simple CASE expression operates by comparing the first expression
to the expression in each WHEN clause for equivalency. If these
expressions are equivalent, the expression in the THEN clause will be
returned.
Allows only an equality check.

Because anything = NULL is always false in ANSI SQL (and if you didn't know this, you need to read up on NULLs in SQL more generally, particularly also with the behavior in the other searched comparison - WHERE x IN (a, b, c)), you cannot use NULL in a simple case and have it ever be compared to a value, with a NULL either in the initial expression or in the list of expressions to be compared against.

If you want to check for NULL, you will have to use an IF/ELSE construct or the searched CASE with a full expression.

I agree that it's kind of unfortunate there is no version which supports an IS comparison to make it easier to write:

select case colname
   when IS NULL then 0
   else 1
end

Which would make writing certain long CASE statements easier:

select case colname
   when IS NULL then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

But that's just wishful thinking...

An option is to use ISNULL or COALESCE:

select case COALESCE(colname, 999999) -- 999999 is some value never used
   when 999999 then ''
   when 1 then 'a'
   when 2 then 'b'
   when 3 then 'c'
   when 4 then 'd'
   else 'z'
end

But it isn't always a great option.

回复收藏 0 原文

一直在等你来 2024-12-09 05:43:32

除了其他答案之外，您还需要稍微更改 CASE 的语法才能执行此操作：

SELECT CASE 
    WHEN NULL IS NULL THEN 0
    ELSE 1
    END;

在语法中使用值隐式使用 equals 比较。 NULL 是未知的，NULL = NULL 也是未知的，因此使用您当前的代码，您将始终得到 ~~zero~~ 1（天哪，我也这样做了）。

要获得所需的行为，可以使用 SET ANSI_NULLS ON ；但请注意，这可能会以您可能无法预测的方式更改其他代码，并且该设置已被弃用 - 因此它将在 SQL Server 的未来版本中完全停止工作（请参阅此 SQL Server 2008 文档）。

In addition to the other answers, you need to change the syntax for CASE slightly to do this:

SELECT CASE 
    WHEN NULL IS NULL THEN 0
    ELSE 1
    END;

Using the value in your syntax implicitly uses an equals comparison. NULL is unknown, and so is NULL = NULL, so with your current code you will always get ~~zero~~ 1 (geez I did it too).

To get the behavior you want, you can use SET ANSI_NULLS ON; however note that this can change other code in ways you may not be able to predict, and the setting is deprecated - so it will stop working at all in a future version of SQL Server (see this SQL Server 2008 doc).

回复收藏 0 原文