NOT IN 与 NOT EXISTS 的不同行为

发布于 2025-01-14 20:26:37 字数 1123 浏览 1 评论 0原文

我从以下两个查询中收到不同的结果,但不明白为什么。此 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

独孤求败 2025-01-21 20:26:37

两个给定的查询应该产生相同的结果,因为您有SELECT 1NOT IN(这对于IN没有什么意义)代码> / <代码>不在开始)。

当比较涉及 null 值时,NOT EXISTSNOT IN 不同

SELECT NOT EXISTS (SELECT NULL::int);  -- false
SELECT 1 NOT IN   (SELECT NULL::int);  -- null !    

NOT EXISTS 为 false
当涉及 NULL 值时,NOT IN 会有所不同。其背后的逻辑:null 值被认为是“未知”,它们的真实值毕竟可能匹配,只是我们不知道。因此结果是 null 而不是 false

对于此用例,不鼓励使用NOT IN.
NOT EXISTS 几乎总是使用子查询的更好的工具。通常也更快。

请参阅:

我的猜测是您显示的 NOT IN 查询与您实际测试的不同。给定 NOT EXISTS 子句的典型“等效”NOT IN 表述为:

...
AND (w.cname, w.pid) NOT IN (
   SELECT w1.cname, k.pid2
   FROM   worksFor w1, Knows k
   WHERE  w1.pid = k.pid1
   AND    w1.pid <> w.pid);

“等效” 带引号,因为它与 不同>null 值。

The two given queries should produce the same result since you have SELECT 1 with NOT IN (which makes little sense for IN / NOT IN to begin with).

NOT EXISTS and NOT IN differ when null values are involved in the comparison:

SELECT NOT EXISTS (SELECT NULL::int);  -- false
SELECT 1 NOT IN   (SELECT NULL::int);  -- null !    

NOT EXISTS is false if the bracket expression returns any rows.
NOT IN differs when NULL 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 is null instead of false.

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 given NOT EXISTS clause would be:

...
AND (w.cname, w.pid) NOT IN (
   SELECT w1.cname, k.pid2
   FROM   worksFor w1, Knows k
   WHERE  w1.pid = k.pid1
   AND    w1.pid <> w.pid);

"Equivalent" in quotes, because it differs with null values.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文