sql - 为什么 SET ANSI_NULLS OFF 不能用于比较字段？

Question

我会认为 SET ANSI_NULLS OFF 模式非常方便，如果它始终如一地工作，我会默认使用它 - 允许在任何地方使用 = 或 != 来比较空值。但只有当我与显式内联 null 进行比较时它才有效，但如果我与包含 null 的字段进行比较 - 它不起作用。微软为什么要这样设计呢？难怪为什么没有人默认使用 SET ANSI_NULLS OFF 模式。我宁愿忍受“is null”和“is not null”的痛苦，也不愿思考我使用 inline 和不使用什么。

这是显示问题的示例 - 符合 ???应该返回 2 行，但它只返回一行。

CREATE TABLE t1 (a int null, b int null)
INSERT INTO t1 values (null,null)
INSERT INTO t1 values (0,null)
INSERT INTO t1 values (null,0)
INSERT INTO t1 values (0,0)
set ansi_nulls ON
select * from t1 where a=null -- empty result
select * from t1 where a=b  -- (0,0)
select * from t1 where null=null -- empty result
set ansi_nulls OFF
select * from t1 where a=null -- (null,null),(null,0)
select * from t1 where a=b -- why only (0,0) ???, it should be (0,0),(null,null)
select * from t1 where null=null -- returns all 4 rows
drop table t1

Answer 1

这里回答了这个问题:

How does ANSI_NULLS work in TSQL?

设置 ansi_nulls off 仅在其中一个操作数是 null 变量或文字 null 时影响比较。

此外，您不应该使用 off 设置编写任何新代码，因为将来不会支持它:

http://msdn.microsoft.com/en-us/library/ms188048.aspx

更新:

在重新阅读您的问题后，我认为您实际上是在说为什么不这样做:

select * from t1 where a=b

函数如下:

select * from t1 where a=b or (a=null and b=null)

在您的示例中将返回 (null, null), (0,0)。

因此，您要检查两个条件:

1. 两列值相等
2. 两列的值都是未知的

当你的表达式写成 a=null 时，你想做什么就很清楚了。但是，如果写成 a=b，您是打算只检查第一个条件，还是同时检查两个条件？

另一种思考方式，假设您正在编写查询以返回具有相同家庭电话号码和手机号码的所有客户。您是否希望返回在这两列中都具有空值的客户？

简而言之，MSFT 不想在只声明一个条件时自动检查两个条件 - 我认为这是正确的设计。