如果子查询包含 NULL，SQL select with "IN"子查询不返回任何记录

Question

我遇到了这种有趣的行为。我看到 left-join 是要走的路，但仍然希望清除它。这是一个错误还是设计行为？有什么解释吗？

当我从左表中选择记录时，在右表的子查询结果中不存在值时，如果子查询结果为空值，则不会返回预期的“缺失”记录。我希望编写此查询的两种方法是等效的。

谢谢!

declare @left table  (id int not null primary key identity(1,1), ref int null)
declare @right table (id int not null primary key identity(1,1), ref int null)

insert @left (ref) values (1)
insert @left (ref) values (2)

insert @right (ref) values (1)
insert @right (ref) values (null)

print 'unexpected empty resultset:'
select * from @left
where ref not in (select ref from @right)

print 'expected result - ref 2:'
select * from @left
where ref not in (select ref from @right where ref is not null)

print 'expected result - ref 2:'
select l.* from @left l
  left join @right r on r.ref = l.ref
where r.id is null

print @@version

给出:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
unexpected empty resultset:
id          ref
----------- -----------

(0 row(s) affected)

expected result - ref 2:
id          ref
----------- -----------
2           2

(1 row(s) affected)

expected result - ref 2:
id          ref
----------- -----------
2           2

(1 row(s) affected)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
    Apr  2 2010 15:48:46 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

Answer 1

这是设计使然。如果匹配失败并且集合包含 NULL，则结果为 NULL，如 SQL 标准所指定。

'1' IN ('1', '3') => true'2' IN ('1', '3') => false'1' IN ('1', NULL) => true'2' IN ('1', NULL) => NULL'1' NOT IN ('1', '3') => false'2' NOT IN ('1', '3') => true'1' NOT IN ('1', NULL) => false'2' NOT IN ('1', NULL) => NULL

---

Informally, the logic behind this is that NULL can be thought of as an unknown value. For example here it doesn't matter what the unknown value is - '1' is clearly in the set, so the result is true.

'1' IN ('1', NULL) => true

在下面的例子中，我们不能确定 '2' 在集合中，但由于我们不知道所有的值，我们也不能确定它不在集合中。所以结果是NULL。

'2' IN ('1', NULL) => NULL

另一种看待它的方式是重写 x NOT IN (Y, Z)如 X <> Y AND X <> Z .那么就可以使用 three-valued logic的规则了:

true AND NULL => NULL
false AND NULL => false