X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E228C9DCAA1 for ; Thu, 9 Feb 2006 05:00:03 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85986-09 for ; Thu, 9 Feb 2006 05:00:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp3.lc.anpe.fr (unknown [80.124.143.18]) by postgresql.org (Postfix) with ESMTP id 9E07D9DC854 for ; Thu, 9 Feb 2006 05:00:01 -0400 (AST) Received: from HOMERE03.anpe.fr (localhost.localdomain [127.0.0.1]) by smtp3.lc.anpe.fr (antivirus) with ESMTP id D430973C91 for ; Thu, 9 Feb 2006 09:59:57 +0100 (CET) Received: from [10.0.32.170] ([10.0.32.170]) by HOMERE03.anpe.fr with Microsoft SMTPSVC(6.0.3790.1830); Thu, 9 Feb 2006 09:59:36 +0100 Message-ID: <43EB048B.6050203@anpe.fr> Date: Thu, 09 Feb 2006 09:59:55 +0100 From: Patrick JACQUOT User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-sql@postgresql.org Subject: Re: Non Matching Records in Two Tables References: <1139433024.2044.48.camel@localhost.localdomain> In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Feb 2006 08:59:36.0149 (UTC) FILETIME=[26B5C850:01C62D57] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.202 required=5 tests=[AWL=0.013, MISSING_HEADERS=0.189] X-Spam-Score: 0.202 X-Spam-Level: X-Archive-Number: 200602/95 X-Sequence-Number: 24105 Ken Hill wrote: > I need some help with a bit of SQL. I have two tables. I want to find > records in one table that don't match records in another table based > on a common column in the two tables. Both tables have a column named > 'key100'. I was trying something like: > > SELECT count(*) > FROM table1, table2 > WHERE (table1.key100 != table2.key100); > > But the query is very slow and I finally just cancel it. Any help is > very much appreciated. > > -Ken Maybe you could use a NOT EXISTS subquery, as in SELECT count(*) from table1 WHERE NOT EXISTS(SELECT count(*) from table2 WHERE table1.key100 =table2.key100) which gives you the number of records in table1 without corresponding records in table2. That kind of query is quite fast, if there exists an index on table2.key100 hth P.Jacquot