Message-ID: <43EA6333.7050604@logix-tt.com>
Date: Wed, 08 Feb 2006 22:31:31 +0100
From: Markus Schaber <schabi@logix-tt.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: ken@scottshill.com
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
Content-Transfer-Encoding: 7bit

Hi, Ken,

Ken Hill schrieb:
> 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.

Do you have indices on the key100 columns? Is autovacuum running, or do
you do analyze manually?

Can you send us the output from "EXPLAIN ANALYZE [your query]"?

Btw, I don't think this query will do what you wanted, it basically
creates a cross product, that means if your tables look like:

schabitest=# select * from table1;
 key100 | valuea | valueb
--------+--------+--------
      1 | foo    | bar
      2 | blah   | blubb
      3 | manga  | mungo

schabitest=# select * from table2;
 key100 | valuec | valued
--------+--------+--------
      1 | monday | euro
      2 | sunday | dollar
      4 | friday | pounds

Then your query will produce something like:
schabitest=# select * from table1, table2 WHERE (table1.key100 !=
table2.key100);
 key100 | valuea | valueb | key100 | valuec | valued
--------+--------+--------+--------+--------+--------
      1 | foo    | bar    |      2 | sunday | dollar
      1 | foo    | bar    |      4 | friday | pounds
      2 | blah   | blubb  |      1 | monday | euro
      2 | blah   | blubb  |      4 | friday | pounds
      3 | manga  | mungo  |      1 | monday | euro
      3 | manga  | mungo  |      2 | sunday | dollar
      3 | manga  | mungo  |      4 | friday | pounds

I suggest you would like to have all records from table1 that don't have
a corresponding record in table2:

schabitest=# select * from table1 where table1.key100 not in (select
key100 from table2);
 key100 | valuea | valueb
--------+--------+--------
      3 | manga  | mungo

HTH,
Markus