NOT IN 子句中的 NULL 值

2021-12-01 00:00:00 sql null tsql sql-server notin

当我得到不同的记录计数时出现了这个问题,我认为是相同的查询,一个使用 not in 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.

推荐答案

Query A 同:

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

因为 3 = 3 为真,所以你得到了结果.

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 为真,因此谓词的计算结果为真,并且您得到一行.

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

相关文章