Postgresql 中的 NOT EXISTS 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11238642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NOT EXISTS clause in Postgresql
提问by cheng
Anyone knows how to perform such query in Postgresql?
任何人都知道如何在 Postgresql 中执行这样的查询？
SELECT *
FROM tabA
WHERE NOT EXISTS (
SELECT *
FROM tabB
WHERE tabB.id = tabA.id
)
When I execute such query, postgresql complains "ERROR: Greenplum Database does not yet support that query."
当我执行这样的查询时，postgresql 会抱怨“ ERROR: Greenplum Database does not yet support that query.”。
EDIT: And how about this one:
编辑：这个怎么样：
SELECT *
FROM tabA
WHERE NOT EXISTS (
SELECT *
FROM tabB WHERE tabB.id = tabA.id AND tabB.id2 = tabA.id2
)
EDIT:
I tested in postgresql 8.2.15 for the 4 answers provided by @ypercube. Conclusions are:
1) The first does not work in this version of postgresql, as I said above in the question. The error message can be found there too.
2) For the other three answers, the execution speed is: (3)LEFT JOIN > (4)EXCEPT >> (2)NOT IN.
Specifically, for queries that have the same syntax, (3)LEFT JOIN takes about 5580ms, (4)EXCEPT takes about 13502ms, and (2)NOT IN takes more than 100000 (In fact I did not wait util it finished).
Is there any particular reasons for NOT IN clause to be so slow?
Cheng
编辑：
我在 postgresql 8.2.15 中测试了@ypercube 提供的 4 个答案。结论是：
1）第一个在这个版本的 postgresql 中不起作用，正如我上面在问题中所说的。错误消息也可以在那里找到。
2）对于其他三个答案，执行速度为：(3)LEFT JOIN > (4)EXCEPT >> (2)NOT IN。
具体来说，对于具有相同语法的查询，(3)LEFT JOIN 大约需要 5580ms，(4)EXCEPT 大约需要 13502ms，并且 (2)NOT IN 需要超过 100000（实际上我没有等待 util 它完成）。
NOT IN 子句这么慢有什么特别的原因吗？
程
回答by ypercube??
There are 3 (main) ways to do this kind of query:
有 3 种（主要）方法可以执行此类查询：
NOT EXISTScorrelated subqueryNOT INsubqueryLEFT JOINwithIS NULLcheck:
NOT EXISTS相关子查询NOT IN子查询LEFT JOIN带IS NULL支票：
You found that the first way does work in Greenplum. @Marco and @juergen provided the 2nd way. Here's the 3rd one, it may bypass Greenplum's limitations:
您发现第一种方法在 Greenplum 中确实有效。@Marco 和 @juergen 提供了第二种方式。这是第 3 个，它可以绕过 Greenplum 的限制：
SELECT tabA.*
FROM
tabA
LEFT JOIN
tabB
ON tabB.id = tabA.id
AND tabB.id2 = tabA.id2
WHERE tabB.id IS NULL ;
This (4th way) also works in Postgres (which supports EXCEPToperator):
这（第四种方式）也适用于 Postgres（支持EXCEPT运算符）：
SELECT a.*
FROM a
WHERE id IN
( SELECT id
FROM a
EXCEPT
SELECT id
FROM b
) ;
Tested in SQL-Fiddle(that all 4 work in Postgres).
在SQL-Fiddle 中测试（所有 4 个都在 Postgres 中工作）。
回答by Marco Mariani
The part of the error you left out could have pointed you in the right direction. I think it said "DETAIL: The query contains a correlated subquery." So you have to rewrite these with joins or non-correlated subqueries.
您遗漏的错误部分可能已经为您指明了正确的方向。我认为它说“详细信息：查询包含相关子查询。” 所以你必须用连接或不相关的子查询重写这些。
SELECT * FROM tabA WHERE id NOT IN (SELECT id FROM tabB);
As for the second query, try
至于第二个查询，试试
SELECT * FROM tabA WHERE (id, id2) NOT IN (SELECT id, id2 FROM tabB);
回答by juergen d
SELECT * FROM tabA
WHERE id not in (SELECT id FROM tabB)
