Received: from oxmail.ox.ac.uk (oxmail2.ox.ac.uk [163.1.2.1]) by postgresql.org (Postfix) with ESMTP id EA167475A5A for ; Fri, 15 Mar 2002 07:46:15 -0500 (EST) Received: from heraldgate2.oucs.ox.ac.uk ([163.1.2.50] helo=frontend2.herald.ox.ac.uk ident=exim) by oxmail.ox.ac.uk with esmtp (Exim 3.34 #1) id 16lr6A-0006FP-02 for pgsql-sql@postgresql.org; Fri, 15 Mar 2002 12:46:18 +0000 Received: from fgu028.anat.ox.ac.uk ([163.1.29.28] helo=FGU028) by frontend2.herald.ox.ac.uk with esmtp (Exim 3.32 #1) id 16lr6A-0001Fz-00 for pgsql-sql@postgresql.org; Fri, 15 Mar 2002 12:46:18 +0000 From: "Llew Sion Goodstadt" To: Subject: Re: where not exists Date: Fri, 15 Mar 2002 12:46:17 -0000 Message-ID: <004801c1cc1f$663c39c0$1c1d01a3@FGU028> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3311 In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Archive-Number: 200203/251 X-Sequence-Number: 7130 I ended up by using an external programme. NOT EXISTS is just a set difference. Doing set compares is really quick if both sets are sorted. I use CRC64s for the data and just compare the resulting sorted sets of (large CRC 64-bit) numbers. Because everything hashes to a number, the memory requirements are not that bad either (8 bytes per item ~256000 tuples per Mb). The programme is in C++ but is as fast in something like Perl. I.e. comparing millions of rows of data takes 10s of seconds rather than 10s of minutes. Leo > > 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?