SQL“ select where not in subquery”不返回任何结果

免责声明: 我已经解决了这个问题(我认为) ，但是我想把这个问题添加到 Stack Overflow，因为我在任何地方都找不到它。而且，有人可能比我有更好的答案。

我有一个数据库，其中一个表“ Common”被其他几个表引用。我希望查看 Common 表中的哪些记录是孤立的(即，没有来自其他任何表的引用)。

我运行了这个查询:

select *
from Common
where common_id not in (select common_id from Table1)
and common_id not in (select common_id from Table2)

我知道有一些孤立的记录，但是没有记录被归还。为什么不呢？

(如果需要的话，这是 SQLServer。)

405513 次浏览
小开

Table1或 Table2对 common _ id 有一些空值:

select *
from Common
where common_id not in (select common_id from Table1 where common_id is not null)
and common_id not in (select common_id from Table2 where common_id is not null)
小开

我一下子就想到了。

select c.commonID, t1.commonID, t2.commonID
from Common c
left outer join Table1 t1 on t1.commonID = c.commonID
left outer join Table2 t2 on t2.commonID = c.commonID
where t1.commonID is null
and t2.commonID is null

我做了一些测试，这是我的结果。

如果 Table1或 Table2没有在 commonID 上建立索引，那么将进行表扫描，但@patmortech 的查询速度仍然是原来的两倍(对于100K 的行主表)。

如果都没有在 commonID 上建立索引，那么您将获得两个表扫描，其中的差异可以忽略不计。

如果两个查询都是在 commonID 上建立索引的，那么“不存在”查询在1/3的时间内运行。

小开 
SELECT T.common_id
FROM Common T
LEFT JOIN Table1 T1 ON T.common_id = T1.common_id
LEFT JOIN Table2 T2 ON T.common_id = T2.common_id
WHERE T1.common_id IS NULL
AND T2.common_id IS NULL
小开 
select *
from Common c
where not exists (select t1.commonid from table1 t1 where t1.commonid = c.commonid)
and not exists (select t2.commonid from table2 t2 where t2.commonid = c.commonid)
小开

如果希望世界是一个两值的布尔空间，则必须自己防止出现 null (第三值)情况。

不要在列表端写入允许空值的 IN 子句。过滤掉它们！

common_id not in
(
select common_id from Table1
where common_id is not null
)
小开

让我们假设 common _ id 的这些值:

Common - 1
Table1 - 2
Table2 - 3, null

我们希望 Common 中的行返回，因为它在其他任何表中都不存在。然而，零抛出了一个猴子扳手。

对于这些值，查询等效于:

select *
from Common
where 1 not in (2)
and 1 not in (3, null)

这相当于:

select *
from Common
where not (1=2)
and not (1=3 or 1=null)

这就是问题开始的地方。当与空值比较时，答案是未知的。因此查询减少到

select *
from Common
where not (false)
and not (false or unkown)

False 或未知是未知的:

select *
from Common
where true
and not (unknown)

真实和非未知也是未知的:

select *
from Common
where unknown

Where 条件不返回结果未知的记录，因此我们不会得到任何记录。

处理这个问题的一种方法是使用存在操作符而不是 in。“存在”从不返回未知数，因为它对行而不是对列进行操作。(行要么存在，要么不存在; 在行级别没有这种空模糊性!)

select *
from Common
where not exists (select common_id from Table1 where common_id = Common.common_id)
and not exists (select common_id from Table2 where common_id = Common.common_id)
小开
最佳答案

更新:

我博客中的这些文章更详细地描述了这些方法之间的区别:

有三种方法可以进行这样的查询:

  • 返回文章页面

    SELECT  *
FROM    common
LEFT JOIN
table1 t1
ON      t1.common_id = common.common_id
WHERE   t1.common_id IS NULL

  • NOT EXISTS:

    SELECT  *
FROM    common
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    table1 t1
WHERE   t1.common_id = common.common_id
)

  • NOT IN:

    SELECT  *
FROM    common
WHERE   common_id NOT IN
(
SELECT  common_id
FROM    table1 t1
)

When table1.common_id is not nullable, all these queries are semantically the same.

When it is nullable, NOT IN is different, since IN (and, therefore, NOT IN) return NULL when a value does not match anything in a list containing a NULL.

This may be confusing but may become more obvious if we recall the alternate syntax for this:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

这个条件的结果是列表中所有比较的布尔乘积。当然，单个 NULL值产生的 NULL结果也会呈现整个结果 NULL。

我们从来不能明确地说 common_id不等于这个列表中的任何值，因为至少有一个值是 NULL。

假设我们有这些数据:

common


--
1
3


table1


--
NULL
1
2

LEFT JOIN / IS NULL和 NOT EXISTS将返回 3，NOT IN将返回 没什么(因为它总是求值为 FALSE或 NULL)。

在 MySQL中，对于非空列，LEFT JOIN / IS NULL和 NOT IN比 NOT EXISTS有效一点(几个百分点)。如果该列可为空，则 NOT EXISTS是最有效的(同样，效率也不高)。

在 Oracle中，所有三个查询都生成相同的计划(ANTI JOIN)。

在 SQL Server中，NOT IN/NOT EXISTS更有效，因为 LEFT JOIN / IS NULL不能通过其优化器优化到 ANTI JOIN。

在 PostgreSQL中，LEFT JOIN / IS NULL和 NOT EXISTS比 NOT IN更有效率，因为它们被优化为 Anti Join，而 NOT IN使用 hashed subplan(如果子查询太大而无法散列，甚至使用纯 subplan)

小开

这对我很有效:)

选择 * from Common

哪里

Common _ id not in (从表1中选择 ISNULL (common _ id，“虚拟数据”))

Common _ id not in (从表2中选择 ISNULL (common _ id，“虚拟数据”))

小开 
select *,
(select COUNT(ID)  from ProductMaster where ProductMaster.CatID = CategoryMaster.ID) as coun
from CategoryMaster
小开

我有一个例子，我正在查找，因为一个表将值作为一个 double，另一个表作为一个字符串，所以它们不匹配(或者没有强制转换就不匹配)。但只有 不在。就像 选择... 在..。一样。很奇怪，但我觉得我应该分享一下以防其他人遇到这种简单的解决方法。

小开

请按照下面的例子来理解上面的内容 题目:

你也可以访问以下链接了解 反加入

select department_name,department_id from hr.departments dep
where not exists
(select 1 from hr.employees emp
where emp.department_id=dep.department_id
)
order by dep.department_name;

DEPARTMENT_NAME DEPARTMENT_ID
Benefits    160
Construction    180
Contracting 190
.......

但是如果我们在这种情况下使用 NOT IN，我们不会得到任何数据。

select Department_name,department_id from hr.departments dep
where department_id not in (select department_id from hr.employees );

没有发现数据

当(select department_id from hr.employees)返回空值并且整个查询被计算为 false 时，就会发生这种情况。如果我们像下面这样稍微改变一下 SQL 并使用 NVL 函数处理 null 值，我们就可以看到它。

select Department_name,department_id from hr.departments dep
where department_id not in (select NVL(department_id,0) from hr.employees )

现在我们得到了数据:

DEPARTMENT_NAME DEPARTMENT_ID
Treasury    120
Corporate Tax   130
Control And Credit  140
Shareholder Services    150
Benefits    160
....

我们再次获得数据，因为我们已经处理了 NVL 函数的空值。

小开

简短的回答是:

子查询返回的集合中有一个 NULL。您可以通过在完成子查询之前删除 NULL 值来解决这个问题，或者使用 NOT EXISTS谓词而不是 NOT IN，因为它是隐式地这样做的。

长答案(摘自 T-SQL 基础知识，第三版，Itzik Ben-Gan)

这是一个示例: 假设在 Sales.Orders 表中有一个带有 NULL orderid 的订单，因此子查询返回一些整数和一个 NULL 值。

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O);

关于为什么上面的查询返回一个空集的解释:

显然，这里的罪魁祸首是您添加到 Orders 表中的 NULL客户 ID。NULL是子查询返回的元素之一。 让我们从你期望的那部分开始。IN 谓词为下订单的客户(例如，客户85)返回 TRUE，因为这样的客户由子查询返回。NOT 操作符否定 IN谓词; 因此，NOT TRUE变成 FALSE，客户被丢弃。这里的预期行为是，如果已知客户 ID 出现在 Orders 表中，那么您肯定不想返回它。

然而(深吸一口气) ，如果 Customer 的客户 ID 没有出现在 Orders 中的非 NULL 客户 ID 集合中，而 Orders 中也有 NULL客户 ID，那么您就不能确定客户在那里，同样，您也不能确定客户不在那里。困惑？我希望我可以用一个例子来澄清这个解释。

IN谓词为客户返回 UNKNOWN，例如 Orders 中的已知客户 ID 集中没有出现的22。这是因为当您将其与已知的客户 ID 进行比较时，您得到的是 FALSE，而当您将其与 NULL 进行比较时，您得到的是 UNKNOWN。FALSE或 UNKNOWN产生 UNKNOWN。考虑表达式 22 NOT IN (1, 2, <other non-22 values>, NULL)。这个表达式可以改写为 NOT 22 IN (1, 2, …, NULL)。可以将此表达式展开为 NOT (22 = 1 OR 22 = 2 OR … OR 22 = NULL)。计算括号中的每个表达式的真值，得到 NOT (FALSE OR FALSE OR … OR UNKNOWN)，转换为 UNKNOWN0，计算结果为 UNKNOWN。

在应用 NOT操作符之前，UNKNOWN在这里的逻辑含义是它不能是 确定客户 ID 是否出现在集合中，因为 NULL可以表示 这里棘手的部分是用 NOT操作符否定 UNKNOWN仍然会产生 这意味着在不知道客户 ID 是否出现在一个集合中的情况下，它 也不知道它是否出现在集合中。请记住，查询筛选器会丢弃 在谓词的结果中得到 UNKNOWN。

简而言之，当对返回至少一个 NULL的子查询使用 NOT IN谓词时, 查询总是返回一个空集。那么，您可以遵循哪些实践来避免这种麻烦呢? 首先, 当一个列不允许使用 NULLs时，一定要把它定义为 NOT NULL 你写的查询，你应该考虑空值和三值逻辑 查询是否可能处理 NULL，如果是，SQL 对 NULL 的处理是否正确 如果不是，则需要进行干预。例如，我们的查询返回一个空集，因为 如果要检查客户 ID 是否只出现在 应该显式或隐式地排除 NULL ーー为了显式地排除它们, 将谓词 O.custid IS NOT NULL 添加到子查询，如下所示:

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O
WHERE O.custid IS NOT NULL);

还可以使用 NOT EXISTS谓词而不是 NOT IN隐式排除 NULL, 像这样:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid);

回想一下，与 IN不同，EXISTS使用两值谓词逻辑 当子查询偶然遇到 O.custid中的 NULL时，表达式 计算结果为 UNKNOWN，并且该行被过滤掉 NULL病例被自然排除，就好像它们不存在一样。所以 EXISTS最终只能处理 因此，使用 NOT EXISTS比使用 NOT IN更安全。

以上信息摘自第4章-子查询，T-SQL 基础，第三版


提交答案