Re: Select .... where id not in (....) returns 0 incorrectly

On Monday, April 4, 2022 10:47:51 PM CEST David Rowley wrote:
> On Tue, 5 Apr 2022 at 01:21, J. Roeleveld <joost@antarean.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.

I agree with following the standard.

If I would feel really strongly about this (I don't), it would be up to me to 
try and convince others.
And I have got better things to do with my time. :)

--
Joost