# Find the missing numbers in a given range

Hi I have a table that has start and end columns that has the values

``````Start   End
9       10
11      19
29      44
45      51
56      60
``````

Now if I give the range like `15 to 60` I need to return the values that not being covered in the above range.

The output I am expecting is - `20,21,22,23,24,25,26,27,28,52,53,54,55`

How can I retrieve this?

• `Join` to a numbers table. Then just return the values that have `NULL` where your columns are and limit the range. Without seeing what you're actually doing and what you've attempted this is the best I can give you.
– Zane
Mar 9, 2016 at 20:27

In postgresql:

``````CREATE TABLE ranges
(n1 int,
n2 int);

INSERT INTO ranges
VALUES (9,10),(11,19),(29,44),(45,51),(56,60);

SELECT n FROM ranges
RIGHT JOIN generate_series(15,60) n
ON (n1 <= n AND n <= n2) WHERE n1 IS NULL;
``````

I use tables that have a dimdate or count data to do this kind of calculation, but you could also do this using the identity column of a table that has at least 60 sequential identities.

Your other option is to select 60 or so rows from another table, and use the RowNumber() function. You would then right join to that select, and find the null values in your original list.

``````SELECT *
RIGHT JOIN (SELECT ROWNUMBER() as num
FROM SomeTable (any table with 60 rows)
WHERE SomeTable.column1 <= 60)
as SomeTableSubQuery on SomeTableSubQuery.num = StartEnd.numbers
WHERE StartEnd.numbers IS NULL
``````

Sorry if this answer is convoluted. I can be more specific if you give me column names, etc.

EDIT: I thought you meant one column, not two, my bad.

This a solution for MS SQL Server 2008 and above

As alluded to by @Zane, this is best achieved using a numbers table, which for this exercise we'll create and populate with 100 numbers. If you already have your own Numbers table, feel free to substitute it in the final query,

``````if object_id('tempdb..#nums') is not null drop table #nums;
create table #nums ([num] int);
with digits (d) as (
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 union
select 0)
insert #nums (num)
select n.number
from (select i.d + ii.d * 10 as number from digits i cross  join digits ii) as n
where  n.number > 0
order by n.number;
``````

This creates and populates a table with the ranges specified in your original post

``````if object_id('tempdb..#ranges') is not null drop table #ranges;
create table #ranges ([start] int, [end] int);
insert into #ranges ([start], [end])
values(9,10),(11,19),(29,44),(45,51),(56,60);
``````

Now we just use an EXCEPT query to show the missing numbers. The first part of the query just lists the number from 15 to 60. The second part joins the numbers and ranges tables to list all the numbers in the specified ranges. The EXCEPT clause does the heavy lifting to provide the list

``````select n.[num]
from #nums n -- my number table
where n.[num] between 15 and 60
except
select n.[num]
from #nums n
inner join #ranges r -- my ranges table
on n.[num] between r.[start] and r.[end]
``````