SQL: View Representing Missing Ranges

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.

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.