将嵌套的(两个)NOT IN 转换为嵌套的(两个)NOT Exists [英] Convert nested （two） NOT IN into nested (two) NOT Exists

问题描述

试图更好地理解NOT EXISTS.当我们有 NOT IN 时，我们可以总是替换 NOT EXISTS，即使是嵌套的情况吗?

Trying to understand NOT EXISTS better. Can we always replace NOT EXISTS when we have NOT IN, even with nested situation?

我发现了这个类似的问题，它只有一个NOT IN 尝试处理嵌套案例时.我们有两个表，registered 和 preActivity.

I found this similar question, it only has one NOT IN while trying to do with the nested case. We have two tables, registered and preActivity .

注册有mId(字符串)、aId(字符串)、季度(字符串)、年份(整数)和preActivity有aId(字符串)、preAId(字符串)其中

Registered has mId (string), aId (string), quarter (string), year (integer) and preActivity has aId (string), preAId (string) where

> mId is member id, 
> aId is the activity Id, 
> preAId is the prerequisite activity Id.

如果我们有这个带有嵌套 NOT IN 的查询，以找出所有成员在 YMCA 游泳活动(课程)之前已经注册了所有必需的活动(先决条件)课程.我们可以将其转换为两个嵌套的 NOT EXIST 吗?

If we have this query with nested NOT IN to find out all the members have registered all the required activities(prerequisite) class before for activity (class) swimming at YMCA. Can we convert it with to two nested NOT EXIST?

SELECT DISTINCT r.mid
FROM registered r
WHERE r.mid NOT IN (SELECT r.mid
                    FROM preActivity p
                    WHERE p.aid = "swimming" AND 
                    p.preAId NOT IN (SELECT r2.mid
                                     FROM registered r2
                                     WHERE r2.mid = r.mid));

使用这篇文章的提示，我们可以转换其中一个 NOT IN，但第二个需要花费我几个小时.有人可以帮忙解释一下吗?

Using the hint for this post, we can convert one of the NOT IN, but the second one taking me hours. Can someone please help with some explanation ?

这是我目前所拥有的:

SELECT DISTINCT r.mid
FROM registered r
WHERE NOT EXISTS (SELECT r.mid
                  FROM preActivity p
                  WHERE p.aid = "swimming" AND 
                  p.preAId NOT IN (SELECT r2.mid  # how can we compare p.preAId with some rows selected from r2 Notice we don't have preAid field from resistered table (following the idea from the post? 
                                   FROM registered r2
                                   WHERE r2.mid = r.mid));

或者我们不能在这里应用相同的想法，因为它是两个嵌套的情况?

Or we can't apply the same idea here since it is a two nested case ?

推荐答案

首先要记住:[NOT] EXISTS 查询中的 SELECT 无关紧要，因为我们只是在寻找行的存在.您甚至可以编写 SELECT 1/0 而不会出错.所以大多数人写 [NOT] EXISTS (SELECT 1. (我喜欢把所有的都放在一行，把其余的子查询放在新的行上)

First thing to remember: the SELECT in an [NOT] EXISTS query doesn't matter, as we're only looking for the existence of rows. You could even write SELECT 1/0 and not get an error. So most people write [NOT] EXISTS (SELECT 1. (I like to put that all on one line and leave the rest of the subquery on new lines)

其次，NOT IN 查询可以有围绕空列的问题，所以最好总是写一个 NOT EXISTS 代替.

Secondly, a NOT IN query can have issues surrounding null columns, so it's best to always write a NOT EXISTS instead.

现在，如果您分析 [NOT] IN 查询，您将看到半连接位于 SELECT 中的列之前的列上.所以一个查询:

Now, if you analyze an [NOT] IN query, you will see that the semi-join is on the column just before with the column in the SELECT. So a query:

X.colA [NOT] IN
    (SELECT Y.colA FROM Y)

总是可以转换为

[NOT] EXISTS (SELECT 1
    FROM Y
    WHERE Y.colA = X.colA)

另一个有趣的语法，对多列连接或可空列最有用，是:

Another interesting syntax, most useful with multi-column joins or nullable columns, is:

[NOT] EXISTS (
    SELECT X.colA
    INTERSECT
    SELECT Y.colA
    FROM Y)

不要忘记始终在子查询列上使用正确的表别名，如果您弄错了，那么您的查询可能会返回不正确的结果，而您却没有注意到.

例如，这里发生了什么?

For example, what happens here?

[NOT] EXISTS (SELECT 1
    FROM Y
    WHERE X.colA = colA)

---

就您而言，您的第一个 NOT IN 查询有点奇怪.

---

In your case, your first NOT IN query is slightly weird.

您将 r.mid 放在连接的两侧，因此无论如何这实际上变成了 EXISTS.

You are putting r.mid on both sides of the join, so effectively this becomes an EXISTS anyway.

因此您的查询可以改写为:

So your query can be rewritten as this:

select distinct r.mid
    from registered r
    where not exists (select 1
        From preActivity p
        where p.aid = "swimming" and 
        not exists (select 1
            From registered r2
            where r2.mid = r.mid and r2.mid = p.preAId
        )
    );

这篇关于将嵌套的(两个)NOT IN 转换为嵌套的(两个)NOT Exists的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！