sql - 在 postgres 中调整子查询

Question

我在数据库中发现了一些可疑数据。我正在尝试确定某个字段 lastname 是否正确。我在 postgres 中提出了以下查询:

SELECT members."memberID", 
       members.lastname 
  FROM members 
 WHERE members."memberID" NOT IN (SELECT members."memberID" 
                                    FROM members 
                                   WHERE members.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*');

子查询当前匹配普通名称和带有连字符的名称。父查询应显示不匹配该模式的成员。目前，查询需要花费大量时间才能运行(我从未见过它完成)。我不确定为什么需要这么长时间或如何改进它。

Answer 1

不存在

SELECT m."memberID", 
       m.lastname 
  FROM MEMBERS m 
 WHERE NOT EXISTS (SELECT NULL
                     FROM MEMBERS b
                    WHERE b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'
                      AND b."memberID" = m."memberID");

左连接/为空

   SELECT m."memberID", 
          m.lastname 
     FROM MEMBERS m 
LEFT JOIN MEMBERS b ON b."memberID" = m."memberID"
                   AND b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'
    WHERE b."memberID" IS NULL

总结

Quote :

PostgreSQL treats LEFT JOIN and NOT EXISTS equally, using same execution plan for both of them (namely a Hash Anti Join for the example above).

As for NOT IN, which is semantically different since its logic is trivalent and it can return NULL, PostgreSQL tries to take this into account and limits itself to using a filter against a subplan (a hashed subplan for a hashable resultset like in example above).

Since it need to search the hash table for each missing value twice (first time to find the value, second time to find a NULL), this method is a little less efficient.

A plain subplan, which the optimizer can resort to any time it decides the list will not fit into the memory, is very inefficient and the queries that have possibility of using it should be avoided like a plague.

That’s why in PostgreSQL 8.4 one should always use LEFT JOIN / IS NULL or NOT EXISTS rather than NOT IN to find the missing values.

附录

但正如 Andrew Lazarus 指出的那样，如果 MEMBERS 表中没有重复的 memberid，则查询只需:

SELECT m."memberID", 
       m.lastname 
  FROM MEMBERS m 
 WHERE b.lastname ~* '[a-zA-z]+([-][a-zA-Z]+)*'