SQL选择"IN"如果子查询包含NULL，则子查询不返回任何记录 [英] SQL select with "IN" subquery returns no records if the sub-query contains NULL

问题描述

我遇到了这种有趣的行为.我看到左联接是要走的路，但仍然希望清除它.这是设计错误还是行为?有什么解释吗?

I came across this interesting behavior. I see left-join is the way to go, but would still like to have this cleared. Is it a bug or behavior by-design? Any explanations?

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

When I select records from left table, where a value is not present in the result of a subquery on the right table, the expected "missing" record is not returned if the subquery result has nulls. I expected the two ways to write this query to be equivalent.

谢谢！

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)

推荐答案

这是设计使然.如果匹配失败并且该集合包含NULL，则结果为NULL(按照SQL标准指定).

This is by design. If the match fails and the set contains NULL the result is NULL, as specified by the SQL standard.

'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

---

非正式地，其背后的逻辑是可以将NULL视为一个未知值.例如，这里的未知值是什么都没关系-集合中显然有'1'，因此结果为true.

---

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.

In the following example we can't be sure that '2' is in the set, but since we don't know all the values we also can't be sure that it isn't in the set. So the result is NULL.

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

另一种查看方法是将 x NOT IN(Y，Z)重写为 X<>Y和X<>Z .然后，您可以使用三值逻辑:

Another way of looking at it is by rewriting x NOT IN (Y, Z) as X <> Y AND X <> Z. Then you can use the rules of three-valued logic:

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

这篇关于SQL选择"IN"如果子查询包含NULL，则子查询不返回任何记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！