Re: NULLS and <> : Discrepancies ?

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> Could someone explain to me why not eliminating nulls destroys the
>> potential results of the query ? In other words, for any X not null, X
>> not in (some NULLs) is false.

> You already know the answer: comparisons to NULL always evaluate to
> false.

Thomas, I'm surprised at you! Comparisons to NULL do not yield false,
they yield NULL.

So, given

foo NOT IN (bar, NULL)

we may rewrite this as

NOT (foo IN (bar, NULL))

NOT (foo = bar OR foo = NULL)

NOT (false OR NULL)

NOT (NULL)

NULL

On the other hand

foo NOT IN (foo, NULL)

NOT (foo IN (foo, NULL))

NOT (foo = foo OR foo = NULL)

NOT (true OR NULL)

NOT (true)

false

So the correct statement of the behavior is that the result of NOT IN is
always either FALSE or NULL if there are any NULLs involved. This is
perfectly correct if you recall the interpretation of NULL as "don't
know". The truth value of "foo = NULL" is not FALSE, it is UNKNOWN,
because you don't know what the NULL is ... it could be foo.

It happens that WHERE treats a NULL condition result the same as FALSE,
ie don't select the row, but they are not the same thing.

regards, tom lane