Re: possible bug in 8.4 - Mailing list pgsql-hackers

Gregory Stark <stark@enterprisedb.com> writes:
> I think Postgres can't do better because there could be a NULL in the
> subquery. If there's a NULL in the subquery then no record would match.

Yeah.  NOT IN does not have the right semantics to become an antijoin.

FWIW, you can use a NOT EXISTS:

select a.a from a where not exists (select 1 from b where a.a = b.a);

In HEAD this should give the identical plan to the leftjoin/is null
hack.
        regards, tom lane