"Kevin Duffy" <[EMAIL PROTECTED]> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql