将IS NULL转换为BIT

Question

这可能是一个非常基础的问题，但我在编写查询时遇到了它。

为什么SQL Server无法将对NULL的检查转换为BIT？ 我在考虑像这样的东西：

DECLARE @someVariable INT = NULL;
-- Do something
SELECT CONVERT(BIT, (@someVariable IS NULL))

预期结果将是 1 或 0。

- diiN__________

---

它有可能，但它并没有。例如MySQL可以并且确实做到了;-) - Álvaro González

@ÁlvaroGonzález MySQL 包含许多非标准语句，甚至违反了 SQL 的逻辑。IS 不能在条件表达式之外使用。它不是比较运算符。在空值逻辑中，任何与 NULL 的比较都是 null。IS NULL 和 IS NOT NULL 是唯一可用的 条件 运算符。 - Panagiotis Kanavos

@PanagiotisKanavos 我明白 foo=NULL 和 foo IS NULL 是不同的（以及为什么）。但是，是否有某些特定情况下 MySQL 实现会出现问题？毕竟，当您使用 WHERE deletion_date IS NULL 时，最终会得到一个布尔非空决策：显示行还是不显示。 - Álvaro González

@ÁlvaroGonzález 是的，有很多。首先，BIT不是BOOLEAN，它只是一个数字。还有一种不同的、可选的BOOLEAN类型，只由PostgreSQL实现。其次，该转换确实打破了3VL。任何与NULL的比较都应返回NULL，但那个任意的转换却没有这样做。如果您要用1代替NULL，只需使用像NULLIF或COALESCE这样的函数。最后，您可以将NULL强制转换为表示特定类型缺失值的NULL。CAST(NULL as BIT)是允许的，并返回一个NULL BIT。 - Panagiotis Kanavos

Answer 1

使用case：

SELECT CONVERT(BIT, (CASE WHEN @someVariable IS NULL THEN 1 ELSE 0 END))

- Gordon Linoff

---

我知道可以使用CASE来实现这一点。但是我想知道为什么SQL Server不知道如何处理@someVariable IS NULL。 - diiN__________

@ÁlvaroGonzález 有很多方法可以得到所需的结果。我只是不明白为什么不能像我尝试的那样使用它。 - diiN__________

@diiN_ 这就是 SQL 的工作方式（不仅仅是 SQL Server），它实际上是有意义的。与 C/C#/Java 不同，SQL 中的值 可以 缺失，即 NULL。NULL 不仅仅是另一个值，它意味着没有办法对该值进行 任何 有意义的比较，甚至不是与 NULL 比较。因此，将 IS NULL 视为布尔表达式是毫无意义的。 - Panagiotis Kanavos

@PanagiotisKanavos 我们可以讨论一下，如果实现了这个功能是否有用或者是否合理，但是“为什么程序X不做Y”是我的客户经常说的话，他们试图通过这种方式获得开发人员的时间并支付费用（我担心这对我的经理有效）。引用雷蒙德·陈的话：“默认情况下，功能不存在。必须有人来实现它们。” - Álvaro González

@ÁlvaroGonzález，你误解了NULL和SQL。IS NULL不是一个表达式。如果MySQL这样处理它，那么它就违反了SQL标准和三值逻辑。这不是讨论的问题 - 这就是SQL的定义。 - Panagiotis Kanavos

Answer 2

或者使用 IIF 函数（比 CASE 更易读）：

CONVERT(BIT, IIF(@x IS NULL, 0, 1))

- Dana

Answer 3

非直接转换

select cast(isnull(@null,1) as bit)

- paparazzo

Answer 4

在SQL语言中，NULL不被认为是数据值。它们代表着缺失或未知状态。引用自Wikipedia's article on SQL NULL:

“SQL null是一种状态（未知），而不是一个值。这种用法与大多数编程语言非常不同，其中null表示未分配给特定实例。”

这意味着对那个UNKNOWN值的任何比较只能是UNKNOWN本身。即使是比较两个NULL也不能返回true：如果两个值都是未知的，我们怎么能说它们相等还是不相等呢？

IS NULL和IS NOT NULL是可以在条件表达式中使用的谓词。这意味着它们本身不返回值。因此，它们不能被“转换”为位，也不能被视为布尔值。

基本的SQL比较运算符在与Null进行比较时总是返回Unknown，因此SQL标准提供了两个特殊的针对Null的比较谓词。IS NULL和IS NOT NULL谓词（使用后缀语法）测试数据是否为Null或不为Null。

任何其他处理Null的方式都是供应商特定的扩展。

最后，BIT不是布尔类型，它只是一个单比特数字。SQL 1999引入了可选的BOOLEAN类型，但只有PostgreSQL正确实现了它，即具有TRUE、FALSE或UNKNOWN值。

没有BOOLEAN类型，您无法真正计算条件表达式的结果，例如A AND B或x IS NULL。您只能使用NULLIF或COALESCE等函数将NULL值替换为其他值。

- Panagiotis Kanavos

---

不错的解释。我可以追加一个问题吗？如果原始问题是关于 SELECT CONVERT(BIT, (@someVariable=123))（即没有NULL参与），你是否会补充其他内容？ - Álvaro González

@ÁlvaroGonzález 不行，因为1）那是无效的语法2）这是一个强制转换内部的赋值还是尝试比较？3）这应该是一个单独的问题，并且有一个同样长的答案，介绍CAST的有效表达式以及标准和供应商实现之间的差异，最后是否可以在SQL中使用比较返回值。 - Panagiotis Kanavos

我肯定误解了这个问题，因为我一直认为这一切都是关于语法的。无论如何，感谢您提供的好答案。 - Álvaro González

ISNULL不等同于NULLIF，它是SQL Server中类似于COALESCE的函数，但只允许两个参数。 - Raul

@RaulSebastian 谢谢你的投票。问题并不是关于 NULLIF，而是关于 NULL 的处理方式以及为什么 IS NULL 不返回值。 - Panagiotis Kanavos

@PanagiotisKanavos 没有未经证实的负评。 - Raul