Re: "WHERE col NOT IN" yields falsely empty result.

pgsql-bugs(at)postgresql(dot)org writes:
> Since the difference set bar - foo is nonempty, the above
> should yield one or more rows. However, the presence of a null
> in foo.col1 (tests 3 and 8 below) yields zero rows, even where
> the difference set has rows with no null entries (see test 2).

This is the spec-mandated behavior. Think of NULL as "I don't know what
this value is". Unless you get a match to one of the non-null outputs
of the subselect (in which case you can definitely say that the test
value *is* IN the subselect), you are forced to conclude that you don't
know for sure whether the test value is in the set or not. Accordingly,
NOT IN will always yield either FALSE or NULL in this situation.

> Am I missing something obvious? eg in one of the FAQs?

If it isn't in the FAQ, it probably should be ...

regards, tom lane