Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7332165024A for ; Wed, 19 Nov 2008 11:08:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79274-02-3 for ; Wed, 19 Nov 2008 11:08:11 -0400 (AST) X-Greylist: delayed 00:22:48.778224 by SQLgrey-1.7.6 Received: from megazone.bigpanda.com (unknown [64.147.171.210]) by mail.postgresql.org (Postfix) with ESMTP id 5F9E564FE3D for ; Wed, 19 Nov 2008 11:07:59 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id EF3793580D; Wed, 19 Nov 2008 06:44:58 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id ECD1F3525E; Wed, 19 Nov 2008 06:44:58 -0800 (PST) Date: Wed, 19 Nov 2008 06:44:58 -0800 (PST) From: Stephan Szabo To: =?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?= Cc: pgsql-performance@postgresql.org Subject: Re: PostgreSQL NOT IN performance In-Reply-To: <331e40660811190351n6bde6caic5fd8cc3a0f777d2@mail.gmail.com> Message-ID: <20081119062806.Y58658@megazone.bigpanda.com> References: <331e40660811190351n6bde6caic5fd8cc3a0f777d2@mail.gmail.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200811/164 X-Sequence-Number: 31895 On Wed, 19 Nov 2008, [ISO-8859-5] =B2=F6=E2=D0=DB=F6=D9 =C2=D8=DC=E7=D8=E8= =D8=DD wrote: > Query 1: > select * from t1 where id not in (select id from t2); > > Query 2 (gives same result as Q1): > select * from t1 except all (select id from t2); It gives the same result as long as no nulls are in either table. If either table can have a null, the conversion changes the results. In addition, a conversion like the above only happens to work because t1 only has an id column. If t1 had two columns you'd get an error because the two sides of except all must have the same number of columns.