Re: negative queries puzzle

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(at)idealint(dot)co(dot)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(at)postgresql(dot)org
>

--
Gunther Schadow, M.D., Ph.D. gschadow(at)regenstrief(dot)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