On Tue, 5 Apr 2022 at 01:21, J. Roeleveld <joost(at)antarean(dot)org> wrote:
> Personally, I think NULL should be treated as a seperate value and not lead to
> strange behaviour.
I think the rationale behind IN and NOT IN are that c IN(1,2,3) is
equivalent of writing: c = 1 OR c = 2 OR c = 3, whereas NOT IN(1,2,3)
would be the same as c <> 1 AND c <> 2 AND c <> 3. You can imagine
what would happen in the latter case if you replaced 3 with NULL. "c
<> NULL" is NULL therefore, due to the quals being ANDed, will cause
the WHERE clause not to match anything.
In any case, it's what the SQL standard says, so that's the way we do it.
David