Artful Common Queries

Find missing values in a range

from the Artful Common Queries page

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 2024