Get Missing Values From Column

In this blog, we will learn how to get the missing values from a column.

I have a @Tab table, which has an ID column. In this column, some values are missing between the first and last value of the column.

 

 

Now, I will try to find out the all the missing values between 1 and 16.

 

Query 

 DECLARE @Max int;
Declare @Min int;

SET @Min=(SELECT MIN(t.Id) FROM @Tab t);
SET @max=(SELECT MAX(t.Id) FROM @Tab t);
WITH CTE AS
(SELECT @Min AS Col
UNION ALL
SELECT COl+1 as t FROM CTE c
WHERE COl<@max)
SELECT * FROM CTE WHERE CTE.Col NOT In(SELECT Id FROM @Tab t);

Output

 

 

This query returns 4 values: 3, 6, 9, 14. These four values are missing in ID column of @Tab.