Re: [GENERAL] Postgresql sql query - selecting rows outside a join

[Adam Ruth]

On Dec 1, 2003, at 10:15 AM, Graham Leggett wrote:

Hi all,

I am trying to do a query that returns  all rows that are _not_ part of
a join, and so far I cannot seem to find a query that doesn't take 30
minutes or more to run.
The basic query is "select * from tableA where tableA_id NOT IN (select
tableA_id from tableB)".
Is there a more efficient way of doing this?

Regards,
Graham
--
This should be more efficient:

select * from tableA where not exists (select null from tableB where 
tableA_id = tableA.tableA_id);

Version 7.4 fixes the slow IN - NOT IN, but I haven't tested it myself 
yet to see how it compares to this.

Adam Ruth

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster