Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 5EB7E2E0114
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 11 Jan 2008 00:01:24 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 82244-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 11 Jan 2008 00:00:27 -0400 (AST)
Received: from mx01.burntmail.com (mx01.burntmail.com [69.93.121.66])
	by postgresql.org (Postfix) with ESMTP id A29F82E0049
	for <pgsql-performance@postgresql.org>;
	Fri, 11 Jan 2008 00:00:25 -0400 (AST)
Received: from pool-71-163-13-81.washdc.fios.verizon.net ([71.163.13.81]
	helo=[127.0.0.1]) by mx01.burntmail.com with esmtpa (Exim 4.63)
	(envelope-from <guyr-ml1@burntmail.com>) id 1JDB4F-000492-39
	for pgsql-performance@postgresql.org; Thu, 10 Jan 2008 22:00:27 -0600
Message-ID: <4786E9D7.10003@burntmail.com>
Date: Thu, 10 Jan 2008 23:00:23 -0500
From: Guy Rouillier <guyr-ml1@burntmail.com>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: not exists clause
References: <4a5f56930801101215l5ae2d5bdk2db4f1873e0789ca@mail.gmail.com>
In-Reply-To: <4a5f56930801101215l5ae2d5bdk2db4f1873e0789ca@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 080110-0, 01/10/2008), Outbound message
X-Antivirus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200801/62
X-Sequence-Number: 28301

S Golly wrote:
> I cannot get the "not exists" clause of ANSI SQL to execute correctly.
> select t.col11, t.col1... from table1 t where not exists (select 1 from 
> table2 where col2 = t.col1);
> table1 has 40M + rows. if that matters.
>  
> OS is FreeBSD 6.2, postgresql version 8.2.6
>  
> Is it not supported or a bug ?
> thank you for your support.

This is really not a performance question, but a general SQL question.

select * from t1

f1
--
1
2
3

select * from t2

f1
--
1
2

select * from t1
where not exists
(
select 1
from t2
where t2.f1 = t1.f1
)

f1
--
3

-- 
Guy Rouillier