Given a table of rows each representing a numerical range.

CREATE TABLE ranges ( 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:

(1,3) (4,5) (8,10) (16,20)

The result I’d like is:

(6,7) (11,15)

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.

## Advertisement

## Answer

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