Postgresql中的NOT EXISTS子句

Question

有人知道如何在PostgreSQL中执行这样的查询吗？

SELECT * 
FROM tabA 
WHERE NOT EXISTS (
    SELECT * 
    FROM tabB 
    WHERE tabB.id = tabA.id
)

当我执行这个查询时，PostgreSQL会抱怨"ERROR: Greenplum数据库尚不支持该查询"。

编辑：那么这个呢：

SELECT * 
FROM tabA 
WHERE NOT EXISTS (
    SELECT * 
    FROM tabB WHERE tabB.id = tabA.id AND tabB.id2 = tabA.id2
)

编辑：
我在postgresql 8.2.15中测试了@ypercube提供的4个答案。结论如下：

1）第一个答案在这个版本的postgresql中不起作用，正如我在问题中所说的一样。错误消息也可以在那里找到。

2）对于其他三个答案，执行速度为：(3)LEFT JOIN > (4)EXCEPT >> (2)NOT IN。具体来说，对于语法相同的查询，(3)LEFT JOIN 大约需要5580毫秒，(4)EXCEPT大约需要13502毫秒，(2)NOT IN则需要超过100000毫秒（实际上我没有等待它完成）。
为什么NOT IN子句这么慢？有什么特别的原因吗？
Cheng

- cheng

---

PostgreSQL 8.2已经过时，不再受支持。自那以后，“EXISTS”和“NOT EXISTS”查询的工作方式发生了重大变化。http://www.postgresql.org/support/versioning/ - kgrittn

如果你想找到最有效的查询，我认为你首先要检查表上的索引。你没有提到表的大小，但是一个查询需要5秒钟意味着（99%）要么是巨大的表，要么是缺乏索引。我建议你添加一个新的问题，包括两个表的定义（CREATE TABLE），查询和执行计划。 - ypercubeᵀᴹ

Answer 1

有3种（主要）方法可以执行这种类型的查询:

1. NOT EXISTS相关子查询

2. NOT IN子查询

3. LEFT JOIN与IS NULL检查:

您发现第一种方法在Greenplum中有效。@Marco和@juergen提供了第二种方法。这是第三种方法，它可能会绕过Greenplum的限制：

SELECT tabA.* 
FROM 
    tabA 
  LEFT JOIN 
    tabB 
      ON  tabB.id = tabA.id 
      AND tabB.id2 = tabA.id2
WHERE tabB.id IS NULL ;

这种方式（第四种方法）在Postgres中也适用（它支持EXCEPT运算符）：

SELECT a.*
FROM a
WHERE id IN
      ( SELECT id
        FROM a
      EXCEPT
        SELECT id
        FROM b
      ) ; 

在SQL-Fiddle中测试过（所有4个都适用于Postgres）。

- ypercubeᵀᴹ

---

不，它并没有。我认为原因是过滤器“tabB.id IS NULL”在左连接之前应用，而不是在左连接之后应用。 - cheng

如何使用NOT EXISTS实现此查询？你能提供任何帮助吗？ - cheng

假设我有两个表：UserInfo和UserOrder。我想查找没有订单的用户。这个查询“SELECT * FROM UserInfo LEFT JOIN UserOrder ON UserInfo.userid = UserOrder.userid AND UserOrder.orderid IS NULL”返回UserInfo中的所有行。在我的表中，UserOrder中确切地有一行，而UserInfo中有多行不同的用户。 - cheng

让我们在聊天中继续这个讨论。 - cheng

完美，我现在正在使用NOT EXISTS，这可以给出最稳定的结果。而且，NOT IN子查询往往会在低work_mem设置（或大表）下变得非常缓慢和灾难性。 - denis.peplin

Answer 2

你省略的错误信息可能会指导你朝正确的方向去解决问题。我认为它说了“详细信息：查询包含相关子查询。” 因此，你需要使用联接或非相关子查询来重写这些查询。

SELECT * FROM tabA WHERE id NOT IN (SELECT id FROM tabB);

针对第二个查询，请尝试：

SELECT * FROM tabA WHERE (id, id2) NOT IN (SELECT id, id2 FROM tabB);

- Marco Mariani

---

谢谢您很快回复。我更新了我的问题。新的查询怎么样？ - cheng

是的，它说“查询包含相关子查询”。这种类型的查询在MySQL中得到了直接支持。我认为PostgreSQL也支持它。 - cheng

Postgres可以处理更复杂的内容，但由于性能原因，Greenplum不支持某些功能。 - Marco Mariani

无论如何，“SELECT * FROM tabA WHERE (id, id2) NOT IN (SELECT id, id2 FROM tabB)”有效。谢谢。 - cheng

Answer 3

SELECT * FROM tabA 
WHERE id not in  (SELECT id FROM tabB)

- juergen d

---

我更新了我的问题，如何在PostgreSQL中执行更新后的查询？ - cheng

Answer 4

四种做法 ----
为此场景创建

创建名为A的表格，包含姓名和科目

create table a (
name varchar (30),
subject varchar (40)
);

插入值

Insert into a values ('abdul', 'math'), ('mashood','science'), ('rehan','urdu'), ('saqib','art');

创建一个名为B的表，包含姓名和科目

create table b(
name varchar(30),
subject varchar(40)
);

插入值

Insert into b values ('abdul', 'math'), ('mashood','hindi'), ('rehan','farsi'), ('saqib','art'),('xyz','math');

从表 A 中选择：

姓名	科目
阿卜杜勒	数学
马舒德	科学
雷汉	乌尔都语
萨奇布	艺术

从表 B 中选择：

姓名	科目
阿卜杜勒	数学
马舒德	印地语
雷汉	波斯语
萨奇布	艺术
XYZ	数学

1. 不在

select name,subject from b where name not in ( select name from a where a.name=b.name and a.subject=b.subject );

名称	科目
mashood	印地语
rehan	波斯语
xyz	数学

2. 不存在

select name,subject from b where not exists ( select name from a where a.name=b.name and a.subject=b.subject );

名称	科目
mashood	印地语
rehan	波斯语
xyz	数学

3. 加入

SELECT b.* 
FROM b left JOIN a 
ON  b.name = a.name
AND b.subject = a.subject
WHERE a.name IS  NULL ;

姓名	科目
mashood	印地语
rehan	波斯语
xyz	数学

4. 除外

SELECT bm.* FROM b bm
WHERE subject IN 
( SELECT subject FROM b where name=bm.name
EXCEPT
SELECT subject FROM a where name=bm.name
) ;

姓名	科目
mashood	印地语
rehan	波斯语
xyz	数学

- Abdul Munam