将 NOT IN 转换为 NOT EXISTS

在理解 NOT EXISTS 的用法时经历了一场噩梦，主要是如何转换下面的 NOT IN 解决方案，以便我能够真正理解我是如何实现结果的。 在askTom、oracle 论坛和stackoverflow 上有几篇文章，但找不到任何明显有助于理解这个问题的内容。如果我通过我的菜鸟搜索错过了它，我很抱歉。

SELECT s.S_Fname, s.S_Lname
FROM STUDENT s
WHERE s.S_Sex = 'F'
AND S.S_Id NOT IN(SELECT e.S_Id
        FROM ENROLLMENT e
        WHERE e.Mark < 70);

内容上有一些帮助，试图找到在她们注册的任何班级中分数从未低于 70 分的女学生。

当你掌握了它的窍门后，这相当简单：

SELECT s.S_Fname, s.S_Lname
FROM STUDENT s
WHERE s.S_Sex = 'F'
AND S.S_Id NOT IN(SELECT e.S_Id           -- take this line
        FROM ENROLLMENT e
        WHERE e.Mark < 70);

该线基本上比较S.S_Id与所有的e.S_Id来自子查询的值。

现在将其更改为NOT EXISTS并进行平等检查S.S_Id = e.S_Id，在子查询内：

SELECT s.S_Fname, s.S_Lname
FROM STUDENT s
WHERE s.S_Sex = 'F'
AND NOT EXISTS (SELECT e.S_Id          
        FROM ENROLLMENT e
        WHERE (e.Mark < 70)       -- if this is complex, you'll need parentheses
        AND S.S_Id = e.S_Id);

可能的微小改变是认识到(SELECT e.S_Id ...并不真正需要e.S_Id。子查询与EXISTS and NOT EXISTS只需检查是否有行返回，列值并不重要。你可以把SELECT *或那里的常数（SELECT 1很常见）或SELECT NULL甚至SELECT 1/0（是的，那会起作用！）：

SELECT s.S_Fname, s.S_Lname
FROM STUDENT s
WHERE s.S_Sex = 'F'
AND NOT EXISTS (SELECT 1
        FROM ENROLLMENT e
        WHERE e.Mark < 70  
        AND S.S_Id = e.S_Id);

另一个主要考虑因素是，当您以这种方式进行转换时，（看似等效）NOT EXISTS and NOT IN仅当两者都有效时，查询的书写才真正等效S_Id列不可为空。如果e.S_Id列可以为空，NOT IN可能会导致整个查询根本不返回任何行（因为x NOT IN (a, b, c, ...)相当于x<>a AND x<>b AND ...当其中之一时，该条件不可能为真a,b,c... is NULL.)

出于类似的原因，如果s.S_Id可以为空（在这种情况下不太可能，因为它可能是主键，但在其他情况下很重要。）

所以使用它几乎总是更好NOT EXISTS，因为即使任一列可为空（S.S_Id = e.S_Idcheck 会丢弃之前带有 null 的行），通常这种行为是需要的。问题中有很多细节：不存在与不存在，在@Martin Smith 的回答中。您还会发现有一些方法可以转换NOT IN to NOT EXISTS并保留与 null 相关的（不愉快的）行为。