Received: from c014.sfo.cp.net (c014-h003.c014.sfo.cp.net [209.228.12.67]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f21JAjx77479 for ; Thu, 1 Mar 2001 14:10:45 -0500 (EST) (envelope-from ken@oldbs.com) Received: (cpmta 1652 invoked from network); 1 Mar 2001 10:32:32 -0800 Received: from unknown (HELO oldbs.com) (216.91.245.19) by smtp.oldbs.com (209.228.12.67) with SMTP; 1 Mar 2001 10:32:32 -0800 X-Sent: 1 Mar 2001 18:32:32 GMT Message-ID: <3A9E95D4.77C1594@oldbs.com> Date: Thu, 01 Mar 2001 13:32:52 -0500 From: Ken Kline X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.16-22min_for_stan i586) X-Accept-Language: en MIME-Version: 1.0 To: Frank Joerdens CC: pgsql-sql@postgresql.org Subject: Re: Weird NOT IN effect with NULL values References: <20010301191654.A12406@rakete.joerdens.de> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/12 X-Sequence-Number: 1654 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