Message-ID: <4F96F746.1090909@pinpointresearch.com>
Date: Tue, 24 Apr 2012 11:56:06 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: emilu@encs.concordia.ca
CC: pgsql-sql@postgresql.org,
	"pgsql-general@postgresql.org >> \"pgsql-general@postgresql.org\""
	<pgsql-general@postgresql.org>
Subject: Re: [SQL] Simple way to get missing number
References: <4F96B57E.8080306@encs.concordia.ca>
	<4F96C9E9.7000005@pinpointresearch.com>
	<4F96EC90.6070600@encs.concordia.ca>
In-Reply-To: <4F96EC90.6070600@encs.concordia.ca>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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