NOT IN 与 NOT EXISTS 的不同行为
我从以下两个查询中收到不同的结果,但不明白为什么。此 NOT IN
查询不应该返回与 NOT EXISTS
查询相同的结果吗?
SELECT p.pid, p.pname, w.salary
FROM Person p, worksFor w
WHERE p.pid = w.pid AND NOT EXISTS (SELECT *
FROM worksFor w1,Knows k
WHERE w1.pid = k.pid1 AND w1.cname = w.cname AND w1.pid <> w.pid AND k.pid2 = w.pid);
SELECT p.pid, p.pname, w.salary
FROM Person p, worksFor w
WHERE p.pid = w.pid AND 1 NOT IN (SELECT 1
FROM worksFor w1, Knows k
WHERE w1.pid = k.pid1 AND w1.cname = w.cname AND w1.pid <> w.pid AND k.pid2 = w.pid);
pid | pname | salary
------+----------+--------
1002 | Vidya | 45000
1007 | Danielle | 50000
1010 | Linda | 55000
1011 | Nick | 70000
1013 | Lisa | 55000
1016 | YinYue | 55000
1018 | Arif | 50000
1019 | John | 50000
pid | pname | salary
------+----------+--------
1007 | Danielle | 50000
1012 | Eric | 50000
1017 | Latha | 60000
1018 | Arif | 50000
1019 | John | 50000
(5 rows)
I am receiving different results from the following two queries and do not understand why. Shouldn't this NOT IN
query return the same result as the NOT EXISTS
query?
SELECT p.pid, p.pname, w.salary
FROM Person p, worksFor w
WHERE p.pid = w.pid AND NOT EXISTS (SELECT *
FROM worksFor w1,Knows k
WHERE w1.pid = k.pid1 AND w1.cname = w.cname AND w1.pid <> w.pid AND k.pid2 = w.pid);
SELECT p.pid, p.pname, w.salary
FROM Person p, worksFor w
WHERE p.pid = w.pid AND 1 NOT IN (SELECT 1
FROM worksFor w1, Knows k
WHERE w1.pid = k.pid1 AND w1.cname = w.cname AND w1.pid <> w.pid AND k.pid2 = w.pid);
pid | pname | salary
------+----------+--------
1002 | Vidya | 45000
1007 | Danielle | 50000
1010 | Linda | 55000
1011 | Nick | 70000
1013 | Lisa | 55000
1016 | YinYue | 55000
1018 | Arif | 50000
1019 | John | 50000
pid | pname | salary
------+----------+--------
1007 | Danielle | 50000
1012 | Eric | 50000
1017 | Latha | 60000
1018 | Arif | 50000
1019 | John | 50000
(5 rows)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
两个给定的查询应该产生相同的结果,因为您有
SELECT 1
和NOT IN
(这对于IN
没有什么意义)代码> / <代码>不在开始)。当比较涉及
null
值时,NOT EXISTS
和NOT IN
不同:NOT EXISTS 为
false
。当涉及
NULL
值时,NOT IN
会有所不同。其背后的逻辑:null
值被认为是“未知”,它们的真实值毕竟可能匹配,只是我们不知道。因此结果是null
而不是false
。对于此用例,不鼓励使用
NOT IN
.NOT EXISTS
几乎总是使用子查询的更好的工具。通常也更快。请参阅:
我的猜测是您显示的
NOT IN
查询与您实际测试的不同。给定NOT EXISTS
子句的典型“等效”NOT IN
表述为:“等效” 带引号,因为它与
不同>null
值。The two given queries should produce the same result since you have
SELECT 1
withNOT IN
(which makes little sense forIN
/NOT IN
to begin with).NOT EXISTS
andNOT IN
differ whennull
values are involved in the comparison:NOT EXISTS
isfalse
if the bracket expression returns any rows.NOT IN
differs whenNULL
values are involved. The logic behind it:null
values are considered "unknown", their true value might match after all, we just don't know. So the result isnull
instead offalse
.NOT IN
is discouraged for this use case.NOT EXISTS
is almost always the better tool with subqueries. Typically faster, too.See:
My guess is that your displayed
NOT IN
query differs from what you actually tested. A typical "equivalent"NOT IN
formulation for the givenNOT EXISTS
clause would be:"Equivalent" in quotes, because it differs with
null
values.