X-Original-To: pgsql-sql@postgresql.org
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id 0C58747616D
	for <pgsql-sql@postgresql.org>; Tue, 18 Mar 2003 12:16:20 -0500 (EST)
Received: (qmail 21232 invoked by uid 500); 18 Mar 2003 17:30:55 -0000
Date: Tue, 18 Mar 2003 11:30:55 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: outer join versus not exists
Message-ID: <20030318173055.GA21021@wolff.to>
Mail-Followup-To: chester c young <chestercyoung@yahoo.com>,
	pgsql-sql@postgresql.org
References: <20030318165427.52097.qmail@web12703.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030318165427.52097.qmail@web12703.mail.yahoo.com>
User-Agent: Mutt/1.3.25i
X-Archive-Number: 200303/440
X-Sequence-Number: 12479

On Tue, Mar 18, 2003 at 08:54:27 -0800,
  chester c young <chestercyoung@yahoo.com> wrote:
> In pg, is there any performance gain in using outer join with null as
> versus using not exists, eg:
> 
> select t1.* from t1 right join t2 using( id ) where t2.id is null;
> 
> versus
> 
> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);

You can use explain analyze to compare the queries.

Another possible approach is:

select t1.* from t1, (select id from t1 except select id from t2) t3
  where t1.id = t3.id;