Re: finding records not listed in a column, Postgresql

Paul Makepeace <postgresql.org@paulm.com> writes:
> On Sun, Apr 27, 2003 at 08:02:16AM -0700, Aaron Payne wrote:
>> I need the records in table A in which the values in
>> A.objectID are not listed in B.objectID.

> select * from A where objectID not in (select objectID from B);

This is the bog-standard way of doing it, but performance sucks in
current Postgres releases (although 7.4 will change that).  So people
tend immediately to look for workarounds.  The "EXISTS" hack illustrated
in the PG FAQ (item 4.22) is one pretty good way.

> select A.* from A left join B on A.objectID=B.objectID where B.objectID is NULL;

This is a good way only if B.objectID is a unique column --- otherwise
you will get multiple copies of any A row that has multiple matches in
B.  (You could possibly fix that by adding DISTINCT, but at the risk of
losing the performance gain you're after.)

            regards, tom lane