NOT IN子句中的NULL值 [英] NULL values inside NOT IN clause

问题描述

当我以相同的查询获得不同的记录计数时出现此问题，一个查询使用 where约束，另一个使用left join约束. not in约束中的表具有一个空值(错误数据)，该空值导致该查询返回0条记录的计数.我有点理解为什么，但是我可以使用一些帮助来完全理解这个概念.

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null value (bad data) which caused that query to return a count of 0 records. I sort of understand why but I could use some help fully grasping the concept.

简单地说，为什么查询A返回结果而B不返回结果?

To state it simply, why does query A return a result but B doesn't?

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

这是在SQL Server 2005上.我还发现调用set ansi_nulls off会导致B返回结果.

This was on SQL Server 2005. I also found that calling set ansi_nulls off causes B to return a result.

推荐答案

查询A与以下内容相同:

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

由于3 = 3为true，所以会得到结果.

Since 3 = 3 is true, you get a result.

查询B与以下内容相同:

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

打开ansi_nulls时，3 <> null为UNKNOWN，因此该谓词的计算结果为UNKNOWN，并且您不会获得任何行.

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

关闭ansi_nulls时，3 <> null为true，因此该谓词的计算结果为true，您将获得一行.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

这篇关于NOT IN子句中的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！