Re: [SQL] Simple way to get missing number

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