## Re: NULLS and <> : Discrepancies ?

From: Tom Lane Thomas Lockhart "Emmanuel Charpentier,,," , pgsql-hackers(at)postgresql(dot)org Re: NULLS and <> : Discrepancies ? 2000-12-30 00:29:55 12719.978136195@sss.pgh.pa.us (view raw, whole thread or download thread mbox) 2000-12-24 07:57:24 from "Emmanuel Charpentier,,,"  2000-12-29 22:56:32 from Thomas Lockhart   2000-12-30 00:29:55 from Tom Lane pgsql-hackers
```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

```

### pgsql-hackers by date

 Next: From: The Hermit Hacker Date: 2000-12-30 00:39:57 Subject: Re: GNU readline and BSD license Previous: From: Tom Lane Date: 2000-12-30 00:16:49 Subject: Re: GNU readline and BSD license