SQL“选择不在子查询中的位置”不返回任何结果

Question

提问by Jeremy Stein

Disclaimer: I have figured out the problem (I think), but I wanted to add this issue to Stack Overflow since I couldn't (easily) find it anywhere. Also, someone might have a better answer than I do.

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

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).

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

I ran this query:

我运行了这个查询：

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?

我知道有孤立记录，但没有返回记录。为什么不？

(This is SQL Server, if it matters.)

（这是 SQL Server，如果重要的话。）

Answer 1

回答by Quassnoi

Update:

更新：

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

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

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

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
        )

LEFT JOIN / IS NULL：

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_idis not nullable, all these queries are semantically the same.

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

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

当它可以为空时，NOT IN则不同，因为IN（因此，NOT IN）NULL当值与包含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
)

The result of this condition is a boolean product of all comparisons within the list. Of course, a single NULLvalue yields the NULLresult which renders the whole result NULLtoo.

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

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

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

Suppose we have these data:

假设我们有这些数据：

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULLand NOT EXISTSwill return 3, NOT INwill return nothing(since it will always evaluate to either FALSEor NULL).

LEFT JOIN / IS NULL并且NOT EXISTS将返回3，NOT IN将不返回任何内容（因为它总是评估为FALSE或NULL）。

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

在MySQL，以防在不可为空的列上，LEFT JOIN / IS NULL并且NOT IN比NOT EXISTS. 如果该列可以为空，NOT EXISTS则效率最高（同样，不多）。

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

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

In SQL Server, NOT IN/ NOT EXISTSare more efficient, since LEFT JOIN / IS NULLcannot be optimized to an ANTI JOINby its optimizer.

在SQL Server, NOT IN/NOT EXISTS中效率更高，因为它的优化器LEFT JOIN / IS NULL无法将其优化为 an ANTI JOIN。

In PostgreSQL, LEFT JOIN / IS NULLand NOT EXISTSare more efficient than NOT IN, sine they are optimized to an Anti Join, while NOT INuses hashed subplan(or even a plain subplanif the subquery is too large to hash)

在PostgreSQL,LEFT JOIN / IS NULL并且NOT EXISTS比NOT IN,更有效，正弦它们被优化为Anti Join, whileNOT IN使用hashed subplan（subplan如果子查询太大而无法散列，甚至是一个普通的）

Answer 2

回答by Amy B

If you want the world to be a two-valued boolean place, you must prevent the null (third value) case yourself.

如果你想让世界成为一个二值布尔值，你必须自己防止空（第三个值）的情况。

Don't write IN clauses that allow nulls in the list side. Filter them out!

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

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

Answer 3

回答by Jeremy Stein

Table1 or Table2 has some null values for common_id. Use this query instead:

表 1 或表 2 的 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)

Answer 4

回答by Austin Salonen

Just off the top of my head...

就在我的头顶...

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

I ran a few tests and here were my results w.r.t. @patmortech's answer and @rexem's comments.

我进行了一些测试，这是我的结果，来自 @patmortech 的回答和 @rexem 的评论。

If either Table1 or Table2 is not indexed on commonID, you get a table scan but @patmortech's query is still twice as fast (for a 100K row master table).

如果 Table1 或 Table2 未在 commonID 上建立索引，您将获得表扫描，但@patmortech 的查询速度仍然是原来的两倍（对于 100K 行的主表）。

If neither are indexed on commonID, you get two table scans and the difference is negligible.

如果两者都没有在 commonID 上建立索引，则会得到两次表扫描，差异可以忽略不计。

If both are indexed on commonID, the "not exists" query runs in 1/3 the time.

如果两者都在 commonID 上建立索引，则“不存在”查询的运行时间为 1/3。

Answer 5

回答by patmortech

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)

Answer 6

回答by Jeremy Stein

Let's suppose these values for common_id:

让我们假设 common_id 的这些值：

Common - 1
Table1 - 2
Table2 - 3, null

We want the row in Common to return, because it doesn't exist in any of the other tables. However, the null throws in a monkey wrench.

我们希望 Common 中的行返回，因为它不存在于任何其他表中。但是，null 会导致活动扳手。

With those values, the query is equivalent to:

使用这些值，查询等效于：

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

That is equivalent to:

这相当于：

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

This is where the problem starts. When comparing with a null, the answer is unknown. So the query reduces to

这就是问题开始的地方。与 null 比较时，答案是 unknown。所以查询减少到

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

false or unknown is unknown:

false 或 unknown 是未知的：

select *
from Common
where true
and not (unknown)

true and not unkown is also unkown:

true 和 not unkown 也是 unkown：

select *
from Common
where unknown

The where condition does not return records where the result is unkown, so we get no records back.

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

One way to deal with this is to use the exists operator rather than in. Exists never returns unkown because it operates on rows rather than columns. (A row either exists or it doesn't; none of this null ambiguity at the row level!)

解决此问题的一种方法是使用exists 运算符而不是in。Exists 永远不会返回unkown，因为它对行而不是列进行操作。（一行要么存在，要么不存在；在行级别没有这种空歧义！）

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)

Answer 7

回答by manji

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

Answer 8

回答by arced

this worked for me :)

这对我有用:)

select * from Common
where
common_id not in (select ISNULL(common_id,'dummy-data')from Table1)
and common_id not in (select ISNULL(common_id,'dummy-data')from Table2)

从通用中选择 *
在哪里
common_id 不在 (select ISNULL(common_id,'dummy-data')from Table1)
并且 common_id 不在（从表 2 中选择ISNULL(common_id,'dummy-data')）

Answer 9

回答by Donga jayesh

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

Answer 10

回答by ransems

I had an example where I was looking up and because one table held the value as a double, the other as a string, they would not match (or not match without a cast). But only NOT IN. As SELECT ... IN ...worked. Weird, but thought I would share in case anyone else encounters this simple fix.

我有一个例子，我正在查找，因为一个表将值保存为双精度值，另一个作为字符串保存，它们将不匹配（或在没有强制转换的情况下不匹配）。但只有NOT IN。作为SELECT ... IN ...工作。奇怪，但我想我会分享以防其他人遇到这个简单的修复。

SQL“选择不在子查询中的位置”不返回任何结果

提问by Jeremy Stein

回答by Quassnoi

回答by Amy B

回答by Jeremy Stein

回答by Austin Salonen

回答by patmortech

回答by Jeremy Stein

回答by manji

回答by arced

回答by Donga jayesh

回答by ransems

相关推荐

最近更新

标签

SQL“选择不在子查询中的位置”不返回任何结果

提问by Jeremy Stein

回答by Quassnoi

回答by Amy B

回答by Jeremy Stein

回答by Austin Salonen

回答by patmortech

回答by Jeremy Stein

回答by manji

回答by arced

回答by Donga jayesh

回答by ransems

相关推荐

即使 x 远高于所选行，使用 SELECT TOP x 的 SQL 巨大性能差异

SQL 如何列出数据库链接 (Oracle) 中存在的所有表？

从 SQL Server 中的子查询值或其他聚合函数获取平均值

SQL postgres 中 LISTAGG 的等价物是什么？

相关推荐

最近更新

标签