Subject Re: [firebird-support] NOT IN and NULLs
Author Ann W. Harrison
Rick Debay wrote:
> If a WHERE clause contains ACOL NOT IN ([VALUES]) and the data in ACOL
> is NULL, what should be returned?
> I'm always getting false for the statement if the column contains a
> NULL, but if I don't have NULL as one of the IN values I should get back
> true!

What should be returned - logically - is NULL - you can't know of an
unknown value is in a list of values. But for the purpose of a where
clause, NULL is equivalent to false. That's just the way the SQL
committee decided to fold three-value logic into two values. There are
a few places where two nulls are considered equivalent - GROUP BY comes
to mind - but the IN clause isn't one of them.