You have a table named tbl with an integer primary key named id , and you need to know what key values between 0 and 999999 are missing in the table.
The simplest solution is an exclusion join from a virtual table of sequential numbers to the target table:
create or replace view v as select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9; select x.i from ( select a.i*100000 + b.i*10000 + c.i*1000 + d.i*100 + e.i*10 + f.i as i from v a join v b join v c join v d join v e join v f ) x left join tbl on x.i=tbl.id where tbl.id is null order by x.i; Last updated 11 Jul 2012 |