Re: where not exists

Sorry for popping in a bit late here, but you could try this:

1) get items which are orphaned in a.
CREATE TEMP TABLE orphans as
SELECT a.join1, a.join2
FROM a LEFT OUTER JOIN b ON a.join1 = b.join1 AND a.join2 = b.join2
WHERE b.join1 IS NULL AND b.join2 IS NULL

D A

"Llew" <leo(dot)goodstadt(at)anat(dot)ox(dot)ac(dot)uk> wrote in message
news:a65qm1$2k6g$1(at)jupiter(dot)hub(dot)org(dot)(dot)(dot)
> Dear everyone,
> What is the best way of removing rows which are not in another table?
>
> I have these two tables each with millions of rows/tuples.
> They are joined on two fields:
> CREATE TABLE a
> (
> join1 OID,
> join2 OID,
> --a fair number of other fields
> .....
> )
> CREATE TABLE b
> (
> join1 OID,
> join2 OID,
> --a fair number of other fields
> .....
> )
> There are indices on both of them (on "join1, join2").
> At the moment, I am doing
>
> 1) get items which are orphaned in a.
> CREATE TEMP TABLE orphans as
> SELECT join1, join2
> FROM a
> WHERE NOT EXISTS
> (
> SELECT *
> FROM b
> WHERE
> a.join1 = b.join1 AND
> a.join2 = b.join2
> )
> 2) DELETE FROM a where orphans.join1 = a.join1 and orphans.join2=a.join2
> 3) DROP TABLE orphans
>
> This is very slow. Is there a better way?
> Should I first copy all join1. join2 from a and b into temporary tables
> first?
> Do I need to index the temporary tables?
> Surely this is a general enough a problem that optimal sets of solutions
> exists in people's experience.
> Thanks a lot.
>
> Llew
>
>