From: Patrick JACQUOT <patrick.jacquot@anpe.fr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Non Matching Records in Two Tables
Date: Thu, 09 Feb 2006 09:59:55 +0100
Message-ID: <43EB048B.6050203@anpe.fr> (raw)
In-Reply-To: <1139433024.2044.48.camel@localhost.localdomain>
References: <1139433024.2044.48.camel@localhost.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: patrick.jacquot@anpe.fr
Subject: Re: Non Matching Records in Two Tables
In-Reply-To: <43EB048B.6050203@anpe.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox