Re: negative queries puzzle

--- 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