Question: 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?

Answer: 
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;

```
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]

```

For more info on the EXCEPT clause see this [MSDN article](https://msdn.microsoft.com/en-us/library/ms188055(v=sql.100).aspx)

Cheers
Phil