sql - 在SQL Server中， "SET ANSI_NULLS ON"是什么意思？

Question

定义说:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

这是否意味着此查询中不会包含空值？

SELECT Region
FROM employees
WHERE Region = @region

或者 ANSI_NULL 只涉及像这样的查询(其中 WHERE 包含特定单词 NULL)？

SELECT Region
FROM employees
WHERE Region = NULL

Answer 1

这意味着，在第一个示例中使用时，如果 @region 为 NULL，则不会返回任何行，即使表中存在 的行区域为NULL。

当ANSI_NULLS打开时(无论如何你应该始终设置它，因为将来不打开它的选项将被删除)，任何比较操作(至少)操作数为 NULL 会产生第三个逻辑值 - UNKNOWN(与 TRUE 和 FALSE 相对)。

UNKNOWN 值如果尚未确定，则通过任何组合 bool 运算符传播(例如，带有 FALSE 操作数的 AND 或 OR 与 TRUE 操作数)或否定(NOT)。

WHERE 子句用于过滤 FROM 子句生成的结果集，使得 WHERE 子句的总体值必须为 TRUE 表示该行不被过滤掉。因此，如果任何比较产生 UNKNOWN，就会导致该行被过滤掉。

<小时/>

@user1227804 的 answer包括这句话:

If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

来自SET ANSI_NULLS ^*

但是，我不确定它想表达什么意思，因为如果比较两个 NULL 列(例如在 JOIN 中)，比较仍然会失败:

create table #T1 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T1(ID,Val1) select 1,null

create table #T2 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T2(ID,Val1) select 1,null

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and t1.Val1 = t2.Val1

上面的查询返回 0 行，而:

SELECT * FROM #T1 t1 INNER JOIN #T2 t2 
   ON t1.ID = t2.ID 
  AND (   t1.Val1 = t2.Val1 
       OR t1.Val1 IS NULL 
      AND t2.Val1 IS NULL   )

返回一行。因此，即使两个操作数都是列，NULL 也不等于NULL。还有documentation for =没有关于操作数的任何内容:

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

If ANSI_NULLS is set to ON, the result is NULL¹, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

Comparing NULL to a non-NULL value always results in FALSE².

但是，¹ 和 ² 都不正确 - 两个比较的结果都是UNKNOWN。

<小时/>

^*这段文字的神秘含义在多年后终于被发现。它实际上意味着，对于这些比较，该设置没有任何效果，并且它始终表现得就像该设置处于打开状态。如果它指出 SET ANSI_NULLS OFF 是无效的设置，那就更清楚了。