Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 76060475909 for ; Mon, 11 Mar 2002 06:05:37 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g2BAs4J35126 for pgsql-sql@postgresql.org; Mon, 11 Mar 2002 04:54:04 -0600 (CST) (envelope-from news) From: "Dag Arne Matre" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: where not exists Date: Mon, 11 Mar 2002 11:54:44 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 63 Message-ID: References: X-Complaints-To: news@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Archive-Number: 200203/166 X-Sequence-Number: 7046 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" wrote in message news:a65qm1$2k6g$1@jupiter.hub.org... > 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 > >