在 SQLServer 中，“ SETANSI_NULLSON”是什么意思？

定义是:

当 SET ANSI _ NULLS 为 ON 时，使用 WHERE column _ name = NULL 的 SELECT 语句返回零行，即使 column _ name 中存在空值。使用 WHERE column _ name < > NULL 的 SELECT 语句返回零行，即使 column _ name 中有非空值。

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

SELECT Region
FROM employees
WHERE Region = @region

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

SELECT Region
FROM employees
WHERE Region = NULL
157953 次浏览
小开
最佳答案

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.

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 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).

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's answer includes this quote:

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

from SET ANSI_NULLS*

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

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   )

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:

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

If ANSI_NULLS is set to ON, the result is NULL1, 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 FALSE2.

However, both 1 and 2 are incorrect - the result of both comparisons is 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 OFF was the setting that had no effect.

小开

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

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

In that case, ANSI_NULLS ON will not return any rows (because null = null will 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 = null will yield true)

小开

Set ANSI NULLS OFF will make NULL = NULL comparision return 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

While this query will not return any results:

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

https://learn.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.

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 !
小开

SET ANSI_NULLS ON

IT Returns all values including null values in the table

SET ANSI_NULLS off

it Ends when columns contains null values

小开

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.

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

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 is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)

SET ANSI_NULLS OFF

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)

Reference document: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16

小开

I guess the main thing here is:

Never use:

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

because ANSI NULL ON/OFF might change your logic.

Always use:

  • @anything IS NULL
  • @anything IS NOT NULL

because it will work regardless of ANSI NULL

小开

SET ANSI_NULLS ON will not return any row when you apply = , <> , != in where condition. You need to use IS NULL , IS NOT NULL.

SET ANSI_NULLS OFF will always return result when you use = , <> , != , IS NULL , IS NOT NULL.


提交答案