From: Peter Childs <blue.dragon@blueyonder.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: EXCEPT Queries
Date: Thu, 20 Feb 2003 08:29:17 +0000 (GMT)
Message-ID: <Pine.LNX.4.44.0302200822130.5033-100000@RedDragon.Childs> (raw)
In-Reply-To: <3E53D806.5030501@lapcrew.com>
On Wed, 19 Feb 2003, Mark Mitchell wrote:
> 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"
> )
>
Big sub queries are a bad idea. Since we know where the number is
going to be why does this not work?
SELECT a.subscriber_name, a.account_number FROM a WHERE
EXISTS (SELECT b.account_number FROM b WHERE
b.account_number=a.account_number);
it should be a lot quicker if your account_number columns are
indexed.
I hope that helps
Peter Childs
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: blue.dragon@blueyonder.co.uk
Subject: Re: EXCEPT Queries
In-Reply-To: <Pine.LNX.4.44.0302200822130.5033-100000@RedDragon.Childs>
* 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