Message-ID: <3D4978DC.4040305@aurora.regenstrief.org>
Date: Thu, 01 Aug 2002 13:07:24 -0500
From: Gunther Schadow <gunther@aurora.regenstrief.org>
Organization: Regenstrief Institute for Health Care
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:0.9.4) Gecko/20011019 Netscape6/6.2
MIME-Version: 1.0
To: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: Jinn Koriech <lists@idealint.co.uk>,
	PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: negative queries puzzle
References: <20020801015135.6908.qmail@web40011.mail.yahoo.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Another option is to try an outer join from the new to the old
table and then select those rows that carry NULL in the
columns from the old table.

regards
-Gunther

Ludwig Lim wrote:

> --- Jinn Koriech <lists@idealint.co.uk> wrote:
> 
>>hi all,
>>
> 
>>but then to get the entirely new items out i use a
>>sub query which takes
>>for ever
>>
>>SELECT DISTINCT * FROM v_postcode_new WHERE postcode
>>NOT IN ( SELECT
>>postcode FROM v_postcode_old ) ORDER BY postcode
>>ASC;
>>
>>does anyone know of a quicker way to accomplish
>>this?  
>>
> 
>   Try using the "NOT EXIST" clause instead of the "NOT
> IN". The "EXIST" clause utilizes the index while the
> "IN" does not utilizes index (i.e. uses sequential
> scan therefore it is much slower).
> 
>    SELECT DISTINCT *
>    FROM v_postcode_new
>    WHERE NOT EXIST( SELECT postcode
>                     FROM v_postcode_old
>                     WHERE v_postcode_new.postcode =
>                           v_postcode_old.postcode)
>    ORDER BY postcode ASC;
> 
> 
>  
> 
> ludwig.
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


-- 
Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
Medical Information Scientist      Regenstrief Institute for Health Care
Adjunct Assistant Professor        Indiana University School of Medicine
tel:1(317)630-7960                         http://aurora.regenstrief.org