在 SQL Server 中，“SET ANSI_NULLS ON”是什么意思？

In SQL Server, what does "SET ANSI_NULLS ON" mean?

提问by Rodniko

The definition says:

定义说：

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.

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

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

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

SELECT Region
FROM employees
WHERE Region = @region

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

还是ANSI_NULL只关注这样的查询（其中WHERE包含特定单词NULL）？

SELECT Region
FROM employees
WHERE Region = NULL

采纳答案by Damien_The_Unbeliever

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

这意味着 if @regionis不会返回任何行NULL，在您的第一个示例中使用时，即使表中有行 where Regionis NULL。

When ANSI_NULLSis 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 NULLproduces the third logic value - UNKNOWN(as opposed to TRUEand FALSE).

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

UNKNOWNvalues propagate through any combining boolean operators if they're not already decided (e.g. ANDwith a FALSEoperand or ORwith a TRUEoperand) or negations (NOT).

UNKNOWN值传播通过任何结合的布尔运算符，如果他们不已经决定（例如，AND具有FALSE操作数或OR与TRUE操作数）或否定（NOT）。

The WHEREclause is used to filter the result set produced by the FROMclause, such that the overall value of the WHEREclause must be TRUEfor the row to not be filtered out. So, if an UNKNOWNis produced by any comparison, it will cause the row to be filtered out.

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

---

---

@user1227804's answerincludes this quote:

@user1227804 的回答包括这句话：

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

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

from SET ANSI_NULLS^*

来自^*SET ANSI_NULLS

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

但是，我不确定它想说明什么，因为如果NULL比较两列（例如在 a 中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

The above query returns 0 rows, whereas:

上面的查询返回 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)

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

返回一行。因此，即使两个操作数都是列，NULL也不等于NULL. 并且文档=没有关于操作数的任何内容：

When you compare two NULLexpressions, the result depends on the ANSI_NULLSsetting:

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

If ANSI_NULLSis set to OFF, the result of NULLcompared to NULLis TRUE.

Comparing NULLto a non-NULLvalue always results in FALSE².

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

如果ANSI_NULLS设置为ON，则结果为NULL¹，遵循 ANSI 约定，即一个NULL（或未知）值不等于另一个NULL或未知值。

如果ANSI_NULLS设置为OFF，则NULL比较结果NULL为TRUE。

与NULL非NULL值比较总是导致FALSE²。

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

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

---

---

^*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 OFFwas the setting that had no affect.

^*多年后终于发现了这段文字的神秘含义。它的实际含义是，对于这些比较，该设置没有任何影响，并且始终表现为该设置为 ON。如果它声明那SET ANSI_NULLS OFF是没有影响的设置会更清楚。

回答by SWeko

If @Regionis not a nullvalue (lets say @Region = 'South') it will not return rows where the Region field is null, regardless of the value of ANSI_NULLS.

如果@Region不是null值（可以说@Region = 'South'），则无论 ANSI_NULLS 的值如何，它都不会返回 Region 字段为空的行。

ANSI_NULLS will only make a difference when the value of @Regionis null, i.e. when your first query essentially becomes the second one.

ANSI_NULLS 只会在值为@Regionis 时产生影响null，即当您的第一个查询本质上成为第二个查询时。

In that case, ANSI_NULLS ON will not return any rows (because null = nullwill yield an unknown boolean value (a.k.a. null)) and ANSI_NULLS OFF will return any rows where the Region field is null (because null = nullwill yield true)

在这种情况下， ANSI_NULLS ON 将不会返回任何行（因为null = null将产生一个未知的布尔值（又名null））并且 ANSI_NULLS OFF 将返回 Region 字段为空的任何行（因为null = null将产生true）

回答by Joseph Stalin

SET ANSI_NULLS ON

设置 ANSI_NULLS ON

IT Returns all values including null values in the table

IT 返回所有值，包括表中的空值

SET ANSI_NULLS off

设置 ANSI_NULLS 关闭

it Ends when columns contains null values

当列包含空值时结束

回答by Pravat Behuria

If ANSI_NULLS is set to "ON" and if we apply = , <> on NULL column value while writing select statement then it will not return any result.

如果 ANSI_NULLS 设置为“ON”，并且如果我们在编写 select 语句时对 NULL 列值应用 = , <> ，则它将不会返回任何结果。

Example

例子

create table #tempTable (sn int, ename varchar(50))

insert into #tempTable
values (1, 'Manoj'), (2, 'Pankaj'), (3, NULL), (4, 'Lokesh'), (5, 'Gopal')

SET ANSI_NULLS ON

设置 ANSI_NULLS ON

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

SET ANSI_NULLS OFF

设置 ANSI_NULLS 关闭

select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)

回答by user369142

I guess the main thing here is:

我想这里的主要内容是：

Neveruser:

从不用户：

• @anything = NULL
• @anything <> NULL
• @anything != null

• @anything = NULL
• @anything <> NULL
• @anything != null

Always use:

始终使用：

• @anything IS NULL
• @anything IS NOT NULL

• @anything IS NULL
• @anything IS NOT NULL

回答by ProblemSolver

Set ANSI NULLS OFF will make NULL = NULL comparision return true. EG :

设置 ANSI NULLS OFF 将使 NULL = NULL 比较返回 true。EG :

        SET ANSI_NULLS OFF
        select * from sys.tables
        where principal_id = Null

will return some result as displayed below: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

将返回一些结果，如下所示： zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

While this query will not return any results:

虽然此查询不会返回任何结果：

        SET ANSI_NULLS ON 
        select * from sys.tables
        where principal_id = Null

回答by Prasanth V J

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

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 nonnull values in column_name.

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

For e.g

例如

DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL

SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where    @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS


SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where    @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !