"Joshua D. Drake" <jd@commandprompt.com> writes:
> This looks like a NULL vs '' issue. Am I wrong?
No, it's a NULL vs NOT IN issue. Specifically, if the subquery yields
any NULLs and the comparison operator is strict (which nearly all are)
then it's impossible to get a TRUE result from the NOT IN --- the only
possibilities are FALSE (if a match is found among the non-nulls)
or NULL (if not). Standard gotcha for newbie SQL coders.
My recommendation is to use NOT EXISTS instead; it's got less surprising
semantics (and, as of 8.4, it'll get optimized significantly better than
NOT IN).
regards, tom lane