Select all ids that dont exist in database table

Question

I have a table that has 2 columns.

Id, ItemValue,

the id range is between 400000 and 409684.

The id is auto incremented by sql server but there are some gaps in the ids.

For example

so in this case 400004 is missing.

Is there any way to get all these ids that dont exist?

Can I do some sort of select statement where i can go through all the ids and select the item value and where there is no id, have a 0 for item value?

You can find all the IDs that sit in the gaps between the highest and lowest values assigned, but there's no way to reliably know about value below the lowest stored value or above the highest stored one (especially if reseeds are possible). The more important thing is though, why do these gaps matter to you? The values should be treated as opaque blobs that happen to fit in numeric columns, and if gaps are important to you, you're using the wrong tool. — Damien_The_Unbeliever, Jul 03 '14 at 10:56
Here is a [solution using a table valued function](http://stackoverflow.com/a/21852703/2186023) that can generate you any sequence of numbers to join against and thus enabling you to check via left join what numbers are actually not present; and if you were to use that function passing the min and max value for the column you are checking against (like -2,147,483,648 and 2,147,483,647 if using int) I am pretty sure you would catch all gaps ;) — DrCopyPaste, Jul 03 '14 at 11:18

Raj · Answer 1 · 2014-07-03T11:42:50.230

2

Try this

WITH IDRange AS(
Select 400000 AS ID
UNION ALL
SELECT ID+1
FROM IDRange
WHERE ID <= 409684
)
SELECT IR.ID FROM IDRange IR
LEFT OUTER JOIN <your_table> YT
ON IR.ID=YT.ID
WHERE YT.ID IS NULL
OPTION(MAXRECURSION 0)

edited Jul 03 '14 at 11:42

answered Jul 03 '14 at 10:58

Raj

10,653
2
45
52

WITH IDRange AS( Select 400000 AS ID UNION ALL SELECT ID+1 FROM IDRange WHERE ID < 409684 ) ... for the correct boundaries – FJT Jul 03 '14 at 11:19

score 1 · Answer 2 · edited May 23 '17 at 10:26

Here is a general method to do the sort of thing you're after.

First you need to create a table of numbers. A good way of doing this is to create a CTE to create them on the fly, like this (from here: SQL, Auxiliary table of numbers)

--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   FROM cteTally
  WHERE N <= 1000000; -- Whatever the maximum value might be...

Then link it to your table and look for the gaps. Since you have a very specific range in mind, the whole answer looks like this:

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
   SELECT numbers.N
   FROM cteTally AS numbers
   LEFT OUTER JOIN TableName AS yourtable
   ON numbers.N = yourtable.Id
   WHERE yourtable.Id IS NULL
   AND numbers.N BETWEEN 400000 AND 409684; -- Constrain to your required range

score 1 · Answer 3 · answered Jul 03 '14 at 11:06

I would create a numbers table first:

SELECT TOP (1000000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
INTO dbo.Numbers
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers(n)
-- WITH (DATA_COMPRESSION = PAGE)
;

Now you can use a LEFT OUTER JOIN or NOT EXISTS:

SELECT n AS ID 
FROM dbo.Numbers LEFT OUTER JOIN TableName t
    ON n = t.Id
WHERE n BETWEEN (SELECT MIN(id) FROM TableName) AND (SELECT MAX(id) FROM TableName)
AND t.Id IS NULL

Can I do some sort of select statement where i can go through all the ids and select the item value and where there is no id, have a 0 for item value?

Then you just have to replace NULL with 0:

SELECT ISNULL(t.Id, 0) AS Id
FROM dbo.Numbers LEFT OUTER JOIN TableName t
    ON n = t.Id
WHERE n BETWEEN (SELECT MIN(id) FROM TableName) AND (SELECT MAX(id) FROM TableName)

score 1 · Answer 4 · answered Jul 03 '14 at 11:25

DECLARE @low int = 400001, @high int = 400005
DECLARE @t table(id int)

INSERT @t values(400001),(400002),(400003),(400005)

;WITH CTE as
(
SELECT @low id
UNION ALL
SELECT id + 1
FROM CTE WHERE id < @high
)
SELECT id FROM CTE
EXCEPT
SELECT id FROM @t
OPTION (MAXRECURSION 0)

score 0 · Answer 5 · answered Jul 03 '14 at 11:55

I have used IF EXISTS and ELSE

DECLARE @counter INT

DECLARE @QualifyingItems TABLE (
        IdINT, 
        Value MONEY)

SELECT @counter = 400000
WHILE @counter < 409688
BEGIN  

  IF EXISTS(SELECT b.Id, b.Value FROM dbo.Items WHERE Id= @counter) 
    BEGIN
        INSERT INTO @QualifyingItems SELECT b.Id, b.Value FROM dbo.Items b WHERE Id= @counter
    END
ELSE
    BEGIN
        INSERT INTO @QualifyingItems SELECT @counter, 0 
    END
  SELECT @counter = @counter + 1  

END

SELECT * FROM @QualifyingItems

This method is extremely ineffective. – t-clausen.dk Jul 03 '14 at 12:28 — t-clausen.dk, Jul 03 '14 at 12:28

g2server · Answer 6 · 2014-07-03T13:49:49.807

Is there any way to get all these ids that dont exist?

This will return the range of IDs that don't exist.

WITH cte AS
   (SELECT [id], ROW_NUMBER() OVER (ORDER BY id) rw
    FROM MyTable
    WHERE id > 400000 AND id < 409684)
   ,cte2 as
   (
    SELECT id, rw, id-rw as dif FROM cte
   )
   ,cte3 as
   (
    SELECT id, rw, dif, RANK() OVER (ORDER BY dif) difRank from cte2
   )

SELECT c1.id + 1 AS StartRange, c2.ID - 1 AS EndRange
FROM cte3 c1 JOIN cte3 c2 ON c1.difRank != c2.difRank AND c1.rw = c2.rw - 1

Select all ids that dont exist in database table

6 Answers6

Linked