"Kevin Duffy" <KD@wrinvestments.com> writes:
> Can someone explain why the NULL ISINs in Security is causing
> so much grief? I do not get it.
NULL generally is taken as "unknown" in SQL comparisons. So if you have
any nulls in the output of the sub-select, what the upper select sees
is a situation like
where 42 NOT IN (1,2,3, ..., NULL, ...)
Now, if it finds 42 in the subquery output, it can say definitively that
the result of NOT IN is FALSE, because 42 clearly *is* in the output.
However, if it doesn't find a match, then what does that NULL represent?
It's unknown, and therefore whether it's equal to 42 is unknown, and so
the result of the NOT IN is unknown. And WHERE treats an unknown result
the same as FALSE, so you don't get an output row from the upper query.
NOT IN is generally pretty evil and best avoided: the funny behavior
with nulls makes it not only a trap for novices, but hard for the system
to optimize. Consider recasting as NOT EXISTS instead.
regards, tom lane