Message-ID: <4D2B41C2.8020605@vmsinfo.com>
Date: Mon, 10 Jan 2011 12:28:34 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: =?ISO-8859-7?Q?=C3=E9=F9=F1=E3=EF=F2_=C2=E1=EB=EA=E1=ED=E1=F2?=
	<lebiathan@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: "SELECT .. WHERE NOT IN" query running for hours
References: <AANLkTi=ir8MdWX3ecbhfx3AFp1Uv7DWkLG68S26VLkRM@mail.gmail.com>
	<4D2695C9.10204@vmsinfo.com>
	<AANLkTimCc6U-xHigzNcjF3xABadg-yKV_1+kTqXRXhnL@mail.gmail.com>
In-Reply-To: <AANLkTimCc6U-xHigzNcjF3xABadg-yKV_1+kTqXRXhnL@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-7; format=flowed
Content-Transfer-Encoding: 8bit

Γιωργος Βαλκανας wrote:
>
> Are there any particular semantics for the "NOT IN" statement that 
> cause the correlated query to execute for every row of the outter 
> query, as opposed to the "NOT EXISTS" ? Or are there any other 
> practical reasons, related to "IN / NOT IN", for this to be happening? 
> Or is it simply due to implementation details of each RDBMS? I guess 
> the former (or the 2nd one), since, as you say, this is common in most 
> databases, but I would most appreciate an answer to clarify this.
>
> Thanks again!
>
> Best regards,
> George
>
>
>

Well, I really hoped that Bruce, Robert or Greg would take on this one, 
but since there are no more qualified takers, I'll take a shot at this 
one. For the "NOT IN (result of a correlated sub-query)", the sub-query 
needs to be executed for every row matching the conditions on the 
driving table, while the   !EXISTS is just a complement of join. It's 
all in the basic set theory which serves as a model for the relational 
databases.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions