SQL“选择不在子查询中的位置"不返回结果 [英] SQL "select where not in subquery" returns no results

问题描述

免责声明:我已经解决了这个问题(我认为)，但我想将此问题添加到 Stack Overflow，因为我无法(轻松)在任何地方找到它.另外，有人可能有比我更好的答案.

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

I have a database where one table "Common" is referenced by several other tables. I wanted to see what records in the Common table were orphaned (i.e., had no references from any of the other tables).

我运行了这个查询:

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

我知道有孤立记录，但没有返回记录.为什么不呢?

I know that there are orphaned records, but no records were returned. Why not?

(这是 SQL Server，如果重要的话.)

(This is SQL Server, if it matters.)

推荐答案

更新:

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

These articles in my blog describe the differences between the methods in more detail:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN/IS NULL:MySQL

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

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

There are three ways to do such a query:

• LEFT JOIN/IS NULL:

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

不存在:

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

不在:

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

当 table1.common_id 不可为空时，所有这些查询在语义上都是相同的.

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

当它可以为空时，NOT IN 是不同的，因为 IN(因此，NOT IN)返回 NULL 当值与包含 NULL 的列表中的任何内容都不匹配时.

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.

The result of this condition is a boolean product of all comparisons within the list. Of course, a single NULL value yields the NULL result which renders the whole result NULL too.

我们永远不能肯定地说 common_id 不等于此列表中的任何值，因为其中至少有一个值是 NULL.

We never cannot say definitely that common_id is not equal to anything from this list, since at least one of the values is NULL.

假设我们有这些数据:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN/IS NULL 和 NOT EXISTS 将返回 3，NOT IN 将返回 没有(因为它总是会评估为 FALSE 或 NULL).

LEFT JOIN / IS NULL and NOT EXISTS will return 3, NOT IN will return nothing (since it will always evaluate to either FALSE or NULL).

在MySQL中，在不可空列的情况下，LEFT JOIN/IS NULL和NOT IN有点(百分之几) 比 NOT EXISTS 更有效率.如果列可以为空，NOT EXISTS 是最有效的(同样，不是很多).

In MySQL, in case on non-nullable column, LEFT JOIN / IS NULL and NOT IN are a little bit (several percent) more efficient than NOT EXISTS. If the column is nullable, NOT EXISTS is the most efficient (again, not much).

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

In Oracle, all three queries yield same plans (an ANTI JOIN).

在SQL Server中，NOT IN/NOT EXISTS效率更高，因为LEFT JOIN/IS NULL不能被优化器优化为 ANTI JOIN.

In SQL Server, NOT IN / NOT EXISTS are more efficient, since LEFT JOIN / IS NULL cannot be optimized to an ANTI JOIN by its optimizer.

在PostgreSQL中，LEFT JOIN/IS NULL和NOT EXISTS比NOT IN更高效，正弦它们针对 Anti Join 进行了优化，而 NOT IN 使用 hashed subplan(或者甚至是一个普通的 subplan，如果子查询太大，无法散列)

In PostgreSQL, LEFT JOIN / IS NULL and NOT EXISTS are more efficient than NOT IN, sine they are optimized to an Anti Join, while NOT IN uses hashed subplan (or even a plain subplan if the subquery is too large to hash)

这篇关于SQL“选择不在子查询中的位置"不返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！