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

Question

我偶然发现了这个有趣的行为。我知道left-join是可行的方法，但仍然希望将其清除。是bug还是人为设计的行为？有什么解释吗？

当我从左表中选择记录时，如果右表上的子查询结果中没有值，如果子查询结果为空，则不会返回预期的“丢失”记录。我原以为编写这个查询的两种方法是等价的。

谢谢!

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

是的，它就是这样设计的。在攻读LEFT JOIN或NOT IN之间，还有许多其他的考虑因素。您应该查看此link，以便很好地解释此行为。

Answer 2

这就是ANSI委员会认为必须要做的事情。

您可以在查询前加上

set ansi_defaults OFF

你就会得到你所期望的结果。

从SQL-Server7.0开始，Microsoft对遵循ansi标准的要求相当严格。

编辑：

不要反对默认设置。你最终会放弃的。

Answer 3

Mark解释了行为的根本原因。它可以通过多种方式来解决，比如LEFT JOIN，通过从where子句或from select子句中过滤掉内部查询中的空值，使用相关的子查询等等。

以下三个简短的帖子是关于同一主题的案例研究：- NOT IN Subquery return zero rows -Issue，NOT IN Subquery return zero rows -Root Cause，NOT IN Subquery return zero rows -Workarounds