Message-ID: <3E53D806.5030501@lapcrew.com>
Date: Wed, 19 Feb 2003 13:16:22 -0600
From: Mark Mitchell <mark@lapcrew.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.0.2) Gecko/20021120 Netscape/7.01
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: EXCEPT Queries
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I have a query that I must dedup using data in another table.
This can be done quiet easily using an EXCEPT clause as long as both 
queries return the same result set, the problem with that is I only want 
to dedup based on a single columns value not dedup based on entire rows. 
The only other way I can see of doing this is using a NOT IN () clause, 
this seems to take much more time and resources than an EXCEPT 
statement. Is there any way to quickly dedup two tables based on only 
one row?

Here is an example of what I'm currently doing.

TABLE "A"
"SUBSCRIBER_NAME" | "ACCOUNT_NUMBER"
--------------------------------------
BOB               |  000001
JOE               |  000002

TABLE "B"
"SUBSCRIBER_NAME" |  "ACCOUNT_NUMBER"
--------------------------------------
BOB               |   000001

To dedup table "A" using the data in table "B" I could use the 
following, except that the dedup takes place on the whole row when I 
only want it to take place on the "ACCOUNT_NUMBER" column.

SELECT  
"A"."SUBSCRIBER_NAME" , "A"."ACCOUNT_NUMBER"
FROM "A"
EXCEPT
SELECT  
"B"."SUBSCRIBER_NAME" , "B"."ACCOUNT_NUMBER"
FROM "B"


I could use this but it takes WAY to long when both result sets contain 
more than a few hundred records. It seems to increase exponentially the 
more records you add to each result set. The data sets I'm working with 
contain anywhere from 0 to 500000 records.

SELECT  
"A"."SUBSCRIBER_NAME" , "A"."ACCOUNT_NUMBER"
FROM "A"
WHERE
"A"."ACCOUNT_NUMBER"
NOT IN
(
  SELECT  
  "B"."ACCOUNT_NUMBER"
  FROM "B"
)


-- 
Mark Mitchell
www.lapcrew.com
Registered Linux User # 299662
Mandrake 8.0 , RedHat 8.0