Re: Weird NOT IN effect with NULL values

this is kind of weird but it is how it works.
You cannot use equality for null...
Null does not equal Null
Null means no value, since it's not a value
it can't equal anything another no value.

SELECT name
FROM customer
WHERE customer_id NOT IN
(
SELECT customer_id
FROM salesorder
)
and customer_id is not null;

should work

Ken


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.
>
> Regards, Frank