将 NOT IN 转换为 NOT EXISTS [英] Converting NOT IN to NOT EXISTS

问题描述

在理解 NOT EXISTS 的用法时做了个噩梦，主要是如何转换下面的 NOT IN 解决方案，这样我才能真正了解我是如何实现结果的.在 askTom、oracle 论坛和 stackoverflow 上有几篇文章，但找不到任何可以明确帮助理解此问题的内容.如果我通过我的菜鸟搜索错过了它，我深表歉意.

SELECT s.S_Fname, s.S_Lname来自学生WHERE s.S_Sex = 'F'AND S.S_Id NOT IN(SELECT e.S_Id从注册 e哪里 e.Mark <70);

内容方面的一些帮助，试图找到在她们注册的任何课程中从未获得低于 70 分的女学生.

解决方案

当你掌握它的窍门时，它相当简单:

SELECT s.S_Fname, s.S_Lname来自学生WHERE s.S_Sex = 'F'AND S.S_Id NOT IN(SELECT e.S_Id -- 走这条线从注册 e哪里 e.Mark <70);

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

现在将其更改为 NOT EXISTS 并在子查询中放置一个相等性检查 S.S_Id = e.S_Id:

SELECT s.S_Fname, s.S_Lname来自学生WHERE s.S_Sex = 'F'AND NOT EXISTS (SELECT e.S_Id从注册 eWHERE (e.Mark <70) -- 如果这很复杂，则需要括号AND S.S_Id = e.S_Id);

可能的小改动是要意识到 (SELECT e.S_Id ... 并不真正需要 e.S_Id.带有 EXISTS 的子查询> 和 NOT EXISTS 只是检查是否有返回的行，列值无关紧要.您可以在那里放置 SELECT * 或常量(SELECT 1 很常见)或 SELECT NULL 甚至 SELECT 1/0 (是的，那会起作用！):

SELECT s.S_Fname, s.S_Lname来自学生WHERE s.S_Sex = 'F'并且不存在(选择 1从注册 e哪里 e.Mark <70AND S.S_Id = e.S_Id);

另一个主要考虑是，当您以这种方式进行转换时，查询的(看似等价的)NOT EXISTS 和 NOT IN 文字实际上是等价的S_Id 列不可为空.如果 e.S_Id 列可以为空，NOT IN 可能会导致整个查询根本不返回任何行(因为 x NOT IN (a, b, c, ...) 等价于 x<>a AND x<>b AND ... 并且当 a,b,c... 是 NULL.)

出于类似的原因，如果 s.S_Id 可以为空，您将得到不同的结果(在这种情况下不太可能，因为它可能是主键，但在其他情况下很重要.)>

因此，使用 NOT EXISTS 几乎总是更好，因为即使任一列可以为空，它的行为也会有所不同(S.S_Id = e.S_Id 检查将丢弃行之前为 null)，通常这种行为是想要的.问题中有很多细节:NOT IN vs NOT EXISTS，在@Martin Smith 的回答中.您还将找到将 NOT IN 转换为 NOT EXISTS 并保持与空相关(令人不快)行为的方法.

Having a nightmare of a time understanding the usage of NOT EXISTS, primarily how to convert my NOT IN solution below so that I can actually understand how I achieved the results. Have several articles on askTom, the oracle forums and stackoverflow, but can't find anything that clearly helps understand this problem. My apologies if I have missed it through my noobish searching.

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

Bit of assistance with the content, trying to find female students who have never received a mark below 70 in any class they have been enrolled in.

解决方案

It's rather simple, when you get the hang of it:

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

That line basically compares S.S_Id with all the e.S_Id values that come from the subquery.

Now change that to NOT EXISTS and put an equality check S.S_Id = e.S_Id, inside the subquery:

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

Minor possible change is to realize that (SELECT e.S_Id ... does not really need the e.S_Id. Subqueries with EXISTS and NOT EXISTS just check if there are rows returned or not and the column values do not matter. You can put SELECT * or a constant there (SELECT 1 is common) or SELECT NULL or even SELECT 1/0 (Yes, that will work!):

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

Another major consideration is that when you do the conversion this way, the (seemingly equivalent) NOT EXISTS and NOT IN writings of a query are really equivalent only if both S_Id columns are not nullable. If the e.S_Id column is nullable, the NOT IN may result in the whole query to return no rows at all (because x NOT IN (a, b, c, ...) is equivalent to x<>a AND x<>b AND ... and that condition cannot be true when one of the a,b,c... is NULL.)

For similar reasons, you will have different results if the s.S_Id is nullable (that's not very likely in this case as it's probably the primary key but in other cases it matters.)

So it's almost always better to use NOT EXISTS, as it behaves differently even if either column is nullable (the S.S_Id = e.S_Id check will discard rows with null earlier) and usually this behaviour is the wanted one. There are many details in the question: NOT IN vs NOT EXISTS, in the answer by @Martin Smith. You will also find there ways to convert the NOT IN to NOT EXISTS and keep the null related (unpleasant) behaviour.

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