Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f21IWVx72744 for ; Thu, 1 Mar 2001 13:32:31 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [207.172.28.154] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4) with ESMTP id 1212074 for pgsql-sql@postgresql.org; Thu, 01 Mar 2001 10:32:30 -0800 Message-ID: <3A9E95A7.63FE080D@agliodbs.com> Date: Thu, 01 Mar 2001 10:32:07 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: sqllist 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/9 X-Sequence-Number: 1650 Mr. Joerdens, > 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 can see how that bug would happen. You may want to forward your e-mail to pgsql-bugs. Regardless, you'll find that you get faster results (as well as avoiding the NULL bug) if you use the following form of the query: SELECT name FROM customer WHERE NOT EXISTS ( SELECT customer_id FROM salesorder WHERE customer_id = customer.customer_id ); Bruce, you may want to consider editing your next edition to include the above modification. WHERE ... NOT IN is a bad idea for any subselect on medium-large tables. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco