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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] > -- Gunther Schadow, M.D., Ph.D. [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
