Select non-existing rows

Question

Let say I have a table:

ColumnA       ColumnB
---------------------------------
1             10.75
4             1234.30
6             2000.99

How can I write a SELECT query that will result in the following:

ColumnA    ColumnB
---------------------------------
1             10.75
2             0.00
3             0.00
4             1234.30
5             0.00
6             2000.99

Answer 1

You can use a CTE to create a list of numbers from 1 to the maximum value in your table:

; with  numbers as
        (
        select  max(ColumnA) as nr
        from    YourTable
        union all
        select  nr - 1
        from    numbers
        where   nr > 1
        )
select  nr.nr as ColumnA
,       yt.ColumnB
from    numbers nr
left join
        YourTable yt
on      nr.nr = yt.ColumnA
order by
        nr.nr
option  (maxrecursion 0)

See it working at SQL Fiddle.

Answer 2

Please try:

declare @min int, @max int
select @min=MIN(ColumnA), @max=MAX(ColumnA) from tbl

select 
    distinct number ColumnA, 
    isnull(b.ColumnB, 0) ColumnB
from 
    master.dbo.spt_values a left join tbl b on a.number=b.ColumnA
where number between @min and @max

Answer 3

Create a TallyTable (or NumbersTable) - see this question: What is the best way to create and populate a numbers table?

With that table create an insert statement:

INSERT INTO YourTable (ColumnA, ColumnB)
SELECT Number FROM NumberTable
WHERE
    NOT EXISTS (SELECT 1 FROM YourTable WHERE NumberTable.Number = YourTable.ColumnA)
    -- Adjust this value or calculate it with a query to the maximum of the source table
    AND NumberTable.Number < 230130

Answer 4

DECLARE @t TABLE (ID INT,Val DECIMAL(10,2))
INSERT INTO @t (ID,Val) VALUES (1,10.75)
INSERT INTO @t (ID,Val) VALUES (4,6.75)
INSERT INTO @t (ID,Val) VALUES (7,4.75)
declare @MinNo int
declare @MaxNo int
declare @IncrementStep int
set @MinNo = 1
set @MaxNo = 10
set @IncrementStep = 1

;with C as
(
  select @MinNo as Num
  union all 
  select Num + @IncrementStep
  from C 
  where Num < @MaxNo
)      
select Num,
CASE WHEN Val IS NOT NULL THEN Val ELSE 0.00 END AS NUMBER
from C  
LEFT JOIN @t t
ON t.ID = c.Num

Answer 5

You could use a number-table or following trick to generate a sequence which you can LEFT OUTER JOIN with your table. I assume you want to determine the boundaries dynamically:

WITH Seq AS 
(
    SELECT TOP ((SELECT Max(ColumnA)FROM Table1) - (SELECT Min(ColumnA) FROM Table1) + 1) 
        Num = (SELECT Min(ColumnA) FROM Table1)+ Row_number() OVER (ORDER BY [object_id]) -1
    FROM   sys.all_objects) 
SELECT ColumnA = Seq.Num,
       ColumnB = COALESCE(t.ColumnB ,0.00)
FROM   Seq 
LEFT OUTER JOIN Table1 t
    ON Seq.Num = t.ColumnA 

Demo with your sample.

Worth reading: http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

Answer 6

I have my collect of table functions like these.

create function dbo.GetNumbers(@Start int, @End int)
returns @Items table
(
    Item int
)
as 
begin

    while (@Start <= @End)
    begin 
        insert into @Items
        values (@Start)

        set @Start = @Start + 1
    end 

    return

end

Then I can use it to left join to my data table and every value will be there.

declare @min int, @max int
set @min = 10
set @max = 20


select gn.Item
from dbo.GetNumbers(@min, @max) gn

I have similar table functions for date ranges, times, timezones, etc.