Re: Weird NOT IN effect with NULL values

> 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.

Read more in the book.  It covers subqueries with nulls, bottom of pages
96.  Not sure about web URL but it is in the subqueries section titled
"NOT IN and Subqueries with NULL Values".

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026