Postgresql: alternative to WHERE IN respective WHERE NOT IN

Question

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?

Many thanks!

Answer 1

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)

The subquery will return at most 1 row.

Answer 2

Here's a way to filter rows with an INNER JOIN:

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

Note that each version can perform differently; sometimes IN is faster, sometimes EXISTS, and sometimes the INNER JOIN.