Skip to content

SQL: View Representing Missing Ranges

Given a table of rows each representing a numerical range.

    start INTEGER,
    end INTEGER

How could I create a view that represents the “holes” in the ranges? (Ignoring the bounds from -infinity to +infinity).

For example if the table had the data:


The result I’d like is:


I’m using python and sqlite. I’m currently thinking that a using a procedural approach in a sqlite function or python might be the most clear and performant approach.

I’ve seen an approach for finding missing dates that rely on a temp table for all the possible dates, but this method isn’t feasible for integer ranges as they could very large.



I like to do this as:

select (start + 1) as missing_start, (next_start - 1) as missing_ne
from (select t.*,
             (select min(t2.start)
              from t t2
              where t2.start > t.start
             ) as next_start
      from t
     ) t
where next_start > end + 1;

Note that this assumes no overlaps. If overlaps are possible the problem can be more difficult to solve.

3 People found this is helpful