在 SQL Server 中，什么是“SET ANSI_NULLS ON"?意思? [英] In SQL Server, what does "SET ANSI_NULLS ON" mean?

问题描述

定义说:

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中存在空值，使用 WHERE column_name = NULL 的 SELECT 语句也会返回零行.使用 WHERE column_name <> NULL 的 SELECT 语句返回零行，即使 column_name 中有非空值.

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.

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

Does this mean that no nulls will be included in this query?

SELECT Region
FROM employees
WHERE Region = @region

还是 ANSI_NULL 只关注这样的查询(其中 WHERE 包括特定词 NULL)?

Or do ANSI_NULLs concern only queries like this one (where the WHERE includes the specific word NULL)?

SELECT Region
FROM employees
WHERE Region = NULL

推荐答案

这意味着如果 @region 为 NULL ，当你第一次使用时，不会返回任何行例如，即使表中有 Region 为 NULL 的行.

It means that no rows will be returned if @region is NULL, when used in your first example, even if there are rows in the table where Region is NULL.

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

When ANSI_NULLS is on (which you should always set on anyway, since the option to not have it on is going to be removed in the future), any comparison operation where (at least) one of the operands is NULL produces the third logic value - UNKNOWN (as opposed to TRUE and FALSE).

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

UNKNOWN values propagate through any combining boolean operators if they're not already decided (e.g. AND with a FALSE operand or OR with a TRUE operand) or negations (NOT).

WHERE 子句用于过滤FROM 子句产生的结果集，使得WHERE 子句的整体值必须TRUE 表示该行不被过滤掉.因此，如果通过任何比较产生 UNKNOWN，它将导致该行被过滤掉.

The WHERE clause is used to filter the result set produced by the FROM clause, such that the overall value of the WHERE clause must be TRUE for the row to not be filtered out. So, if an UNKNOWN is produced by any comparison, it will cause the row to be filtered out.

@user1227804 的 answer 包含此引述:

@user1227804's answer includes this quote:

如果比较两边都是列或者复合表达式，设置不影响比较.

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

来自 SET ANSI_NULLS^*

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

However, I'm not sure what point it's trying to make, since if two NULL columns are compared (e.g. in a JOIN), the comparison still fails:

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 行，而:

The above query returns 0 rows, whereas:

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.而 = 的文档没有关于操作数没有什么可说的:

Returns one row. So even when both operands are columns, NULL does not equal NULL. And the documentation for = doesn't have anything to say about the operands:

当您比较两个 NULL 表达式时，结果取决于 ANSI_NULLS 设置:

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

如果 ANSI_NULLS 设置为 ON，则结果为 NULL¹，遵循 ANSI 约定NULL(或未知)值不等于另一个 NULL 或未知值.

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.

如果 ANSI_NULLS 设置为 OFF，则 NULL 与 NULL 相比的结果为 TRUE.

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

将 NULL 与非 NULL 值进行比较总是导致 FALSE².

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

然而，¹ 和 ² 都不正确 - 两个比较的结果都是 UNKNOWN.

However, both ¹ and ² are incorrect - the result of both comparisons is UNKNOWN.

^*这段文字的神秘含义在多年后终于被发现.它的实际意思是，对于这些比较，该设置没有任何影响，并且它总是像设置为 ON 一样.如果声明 SET ANSI_NULLS OFF 是无效的设置，那就更清楚了.

^*The cryptic meaning of this text was finally discovered years later. What it actually means is that, for those comparisons, the setting has no effect and it always acts as if the setting were ON. Would have been clearer if it had stated that SET ANSI_NULLS OFF was the setting that had no effect.

这篇关于在 SQL Server 中，什么是“SET ANSI_NULLS ON"?意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！