Re: Weird NOT IN effect with NULL values

On Thu, 1 Mar 2001, Frank Joerdens wrote:

> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
>
> I am using 7.1 beta 4.

I believe it may be actually correct. If my reading of the spec is
correct (which it possibly is not), customer_id NOT IN (subselect) is
effectively, NOT ( customer_id = ANY (subselect) ) and then:

Using the rules for ANY,
If customer_id=<inner customer_id> for at least one row, IN returns true
so NOT IN returns false.
If customer_id=<inner customer_id> is false for every row, IN returns
false so NOT IN returns true.
Otherwise IN and NOT IN both return unknown.

Since customer_id=NULL is unknown, you're getting at least one unknown in
the ANY expression so NOT IN doesn't return true, it returns unknown
which is not sufficient for making the where clause return the row.