hi all,
here's a query i've never been able to improve:
i have an old data set and a new data set - in this case uk postcodes
with eastings and northings. i want to extract the new and changed
postcodes from the new set. to get the changed entries i use a join and
it works okay:
SELECT n.post
On 31 Jul 2002, Jinn Koriech wrote:
> hi all,
>
> here's a query i've never been able to improve:
>
> i have an old data set and a new data set - in this case uk postcodes
> with eastings and northings. i want to extract the new and changed
> postcodes from the new set. to get the changed entr
> 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;
NOT IN is known to be very, very slow in Postgres. Use NOT EXISTS instead:
SELEC
--- 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
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 u
