Select all identifiers that do not exist in the database table.

Question

I have a table with two columns.

Id, ItemValue,

id range is between 400000 and 409684.

The identifier is automatically incremented using the sql server, but there are spaces in the identifiers.

for instance

400001
400002
400003
400005 

therefore, in this case, 400004 is missing.

Is there a way to get all of these identifiers that don't exist?

Is it possible to make some kind of select statement, where I can go through all the identifiers and select the value of the element and where there is no identifier, have a value of 0 for the element?

Answer 1

try it

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)

Answer 2

Here is a general way to do what you need.

. CTE , (: SQL, )

--===== Itzik 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...

. , :

   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

Answer 3

:

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)
;

LEFT OUTER JOIN 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

- select, ids , 0 ?

NULL 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)

Answer 4

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)

Answer 5

IF EXISTS 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 

Answer 6

Is there a way to get all of these identifiers that don't exist?

This will return a range of identifiers that do not 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