Find missing numerical values in range

Question

I've read several articles that one of the mistakes a common programmer does is not using SQL's potential and since then I started searching for replacing parts of my code with SQLish solutions rather than fetching data and processing with a programming language, although I'm a real rookie with SQL.

Say I have a table randomly populated with values from 0 to 10 and I want to know which values are missing in this range.

For example, the table consists these values: 0, 1, 3, 4, 5, 7, 8, 9.

The query should return: 2, 6, 10.

IDK what level of programming you're on, but if you're willing to accept some advice I can assure that you'll have problems with this: "I started searching for replacing parts of my code with SQLish solutions". — Tomasz Kowalczyk, Feb 18 '14 at 11:03

score 0 · Answer 1 · answered Feb 18 '14 at 11:11

0

[F5] solution (assuming sql server):

-- table with id=0..10 
drop table #temp 
GO
create table #temp (
    id int not null identity(0,1),
    x int
)
GO
insert into #temp (x) values(0)
GO 11


-- your number:
drop table #numbers
GO
select
    *
into #numbers
from (
    select 0 as n union all select  1 union all select  3 union all select  4 union all select  5 union all select  7 union all select  8 union all select  9
) x
GO

-- result:
select 
    * 
from #temp t
left join #numbers n
    on t.id=n.n
where 1=1
    and n.n is null

answered Feb 18 '14 at 11:11

AdamL

12,421
5
50
74

@DrCopyPaste Just my habit :). – AdamL Feb 18 '14 at 11:52
that's what I thought, but where does it originate from? Are there some rare occasionse where it gets you a huge benefit? just curious, maybe it's worth adopting that habit :D – DrCopyPaste Feb 18 '14 at 11:55
@DrCopyPaste OK :). 1) If you have 2 `and` conditions, you can comment the first one without breaking the query (otherwise you'd be left with `where and`). 2) It improves readability. If there are multiple `and` conditions, they're just all nicely aligned. But if there have many nested `and`'s and `or`'s, it can get very messy. So I use `1=1 and... and...` and `1=2 or... or...` to get a nice structure of conditions. – AdamL Feb 18 '14 at 12:05
ty for clearing that up, makes sense ;) – DrCopyPaste Feb 18 '14 at 12:17

score 0 · Answer 2 · answered Feb 18 '14 at 11:42

This solution uses SQL-Server-Syntax (but AFAIK only GO is specific to the SQL Server Management Studio)

I would join against a table valued function that gets you all numbers in a certain range (example fiddle):

CREATE FUNCTION dbo.GetNumbersInRange(@Min INT, @Max INT)
        RETURNS @trackingItems TABLE (Number INT) 
        AS BEGIN

        DECLARE @counter INT = @Min

        WHILE (@counter <= @Max)
        BEGIN
          INSERT INTO @trackingItems (Number) SELECT @counter

          SELECT @counter = @counter + 1

        END

        RETURN

        END
        GO

As an example I have set up a table that contains some numbers (with gaps)

CREATE TABLE MyNumbers (Number INT)

INSERT INTO MyNumbers (Number)
  SELECT 1
  UNION
  SELECT 2
  UNION
  SELECT 4
  UNION
  SELECT 5
  UNION
  SELECT 7
  UNION
  SELECT 8

To find the missing numbers you can use a LEFT JOIN like this

SELECT
        AllNumbers.Number
      FROM GetNumbersInRange(1, 10) AS AllNumbers
      LEFT JOIN MyNumbers ON AllNumbers.Number = MyNumbers.Number
      WHERE MyNumbers.Number IS NULL

Find missing numerical values in range

2 Answers2

Linked