Re: "large" IN/NOT IN subqueries result in query returning wrong data

elein <elein(at)varlena(dot)com> writes:

> > Note that the above are not inverses because you changed the lefthand
> > input. You do get consistent results when you just add or omit NOT:
> Yes, you are right. I skipped the permutations to get down to the point.

Remember that NULL means "unknown". So "1 IN (1,2,NULL)" *should* be true
because regardless of what that unknown value is it's still obvious that 1
really is in the list. And "3 NOT IN (1,2,NULL)" is unknown because it depends
on whether that unknown quantity is 3 or not.

IN is the same as "= ANY" so "1 IN (1,2,NULL)" is the same as
"1=1 OR 1=2 OR 1=NULL" which is true even though the last of the three is null.

--
greg