Postgresql: WHERE IN alternative matching WHERE NOT IN

Question

I have several statements that 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 get access to even more tables. What is the best approach to increasing productivity, should you switch, for example, to connections?

Many thanks!

Answer 1

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

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

A subquery will return no more than 1 row.

Answer 2

Here you can filter strings with INNER JOIN:

SELECT     a.id 
FROM       a 
INNER JOIN b ON a.id = b.id

Please note that each version may work differently; sometimes IN is faster, sometimes EXISTS, and sometimes INNER JOIN.

Answer 3

, . .