Received: from candle.pha.pa.us (candle.navpoint.com [162.33.245.46]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f21IgLx73940 for ; Thu, 1 Mar 2001 13:42:21 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id NAA10441; Thu, 1 Mar 2001 13:42:10 -0500 (EST) From: Bruce Momjian Message-Id: <200103011842.NAA10441@candle.pha.pa.us> Subject: Re: Weird NOT IN effect with NULL values In-Reply-To: <20010301191654.A12406@rakete.joerdens.de> "from Frank Joerdens at Mar 1, 2001 07:16:54 pm" To: Frank Joerdens Date: Thu, 1 Mar 2001 13:42:10 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL77 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200103/10 X-Sequence-Number: 1651 > 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, Pennsylvania 19026