X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 530189DCAA7
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Feb 2006 16:28:22 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 25300-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Feb 2006 16:28:22 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from gghcwest.com (adsl-71-128-90-172.dsl.pltn13.pacbell.net
	[71.128.90.172])
	by postgresql.org (Postfix) with ESMTP id 0FB909DC85F
	for <pgsql-performance@postgresql.org>;
	Wed,  1 Feb 2006 16:28:18 -0400 (AST)
Received: from toonses.gghcwest.com (toonses.gghcwest.com [192.168.168.115])
	by gghcwest.com (8.12.10/8.12.9) with ESMTP id k11KSImd020153
	for <pgsql-performance@postgresql.org>; Wed, 1 Feb 2006 12:28:18 -0800
Received: from jwb by toonses.gghcwest.com with local (Exim 4.52)
	id 1F4OaR-0002Eb-JN
	for pgsql-performance@postgresql.org; Wed, 01 Feb 2006 12:28:19 -0800
Subject: Re: Index Usage using IN
From: "Jeffrey W. Baker" <jwbaker@acm.org>
To: pgsql-performance@postgresql.org
In-Reply-To: <1138825370.8376.5.camel@toonses.gghcwest.com>
References: <43E1164B.7020104@telogis.com>
	<1138825370.8376.5.camel@toonses.gghcwest.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Wed, 01 Feb 2006 12:28:19 -0800
Message-Id: <1138825699.8376.8.camel@toonses.gghcwest.com>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.093 required=5 tests=[AWL=0.093, UPPERCASE_25_50=0]
X-Spam-Score: 0.093
X-Spam-Level: 
X-Archive-Number: 200602/26
X-Sequence-Number: 17012

On Wed, 2006-02-01 at 12:22 -0800, Jeffrey W. Baker wrote:
> On Thu, 2006-02-02 at 09:12 +1300, Ralph Mason wrote:
> > Hi,
> > 
> > I have 2 tables both have an index on ID (both ID columns are an oid).
> > 
> > I want to find only only rows in one and not the other.
> > 
> > Select ID from TableA where ID not IN ( Select ID from Table B)
> 
> Have you considered this:
> 
> SELECT ID from TableA EXCEPT Select ID from Table B

Alternately:

   SELECT a.ID 
     FROM TableA AS a 
LEFT JOIN TableB AS b 
       ON a.ID = b.ID 
    WHERE b.ID IS NULL

-jwb