From: Steve Crawford <scrawford@pinpointresearch.com>
To: emilu@encs.concordia.ca
Cc: pgsql-sql@postgresql.org
Cc: " <pgsql-general@postgresql.org>
Subject: Re: [SQL] Simple way to get missing number
Date: Tue, 24 Apr 2012 11:56:06 -0700
Message-ID: <4F96F746.1090909@pinpointresearch.com> (raw)
In-Reply-To: <4F96EC90.6070600@encs.concordia.ca>
References: <4F96B57E.8080306@encs.concordia.ca>
<4F96C9E9.7000005@pinpointresearch.com>
<4F96EC90.6070600@encs.concordia.ca>
On 04/24/2012 11:10 AM, Emi Lu wrote:
> I got it and thank you very much for everyone's help!!
>
> It seems that "left join where is null" is faster comparing with
> "except". And my final query is:
>
> select num as missing
> from generate_series(5000, #{max_id}) t(num)
> left join t1 on (t.num = t1.id)
> where t1.id is null;
>
BTW, there are many options. Two more of them include EXISTS:
select allnumbers from generate_series(1,15) as allnumbers where not
exists (select 1 from fooo where fooo.anumber=allnumbers.allnumbers);
And IN:
select allnumbers from generate_series(1,15) as allnumbers where
allnumbers not in (select anumber from fooo);
They all give you the same result. The "right" choice will depend on the
size of your table, how it is indexed, how fully it is populated and
even on your version of PostgreSQL. (Apologies for the funky field/table
naming.)
Cheers,
Steve
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: scrawford@pinpointresearch.com, emilu@encs.concordia.ca, pgsql-general@postgresql.org
Subject: Re: [SQL] Simple way to get missing number
In-Reply-To: <4F96F746.1090909@pinpointresearch.com>
* 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