Postgresql:替代WHERE IN 各自的WHERE NOT IN [英] Postgresql: alternative to WHERE IN respective WHERE NOT IN

问题描述

我有几个语句可以访问非常大的 Postgresql 表，即:

I have several statements which access very large Postgresql tables i.e. with:

SELECT a.id FROM a WHERE a.id IN ( SELECT b.id FROM b );
SELECT a.id FROM a WHERE a.id NOT IN ( SELECT b.id FROM b );

他们中的一些人甚至以这种方式访问​​更多的表.提高性能的最佳方法是什么，我应该切换，即加入吗?

Some of them even access even more tables in that way. What is the best approach to increase the performence, should I switch i.e. to joins?

非常感谢！

推荐答案

JOIN 会更有效率，或者你可以使用 EXISTS:

JOIN will be far more efficient, or you can use EXISTS:

SELECT a.id FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id)

子查询最多返回 1 行.

The subquery will return at most 1 row.

这篇关于Postgresql:替代WHERE IN 各自的WHERE NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！