Get Missing Values From 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 
  1.  DECLARE @Max int;  
  2. Declare @Min int;  
  3.   
  4. SET @Min=(SELECT MIN(t.Id) FROM @Tab t);  
  5. SET @max=(SELECT MAX(t.Id) FROM @Tab t);  
  6. WITH CTE AS  
  7. (SELECT @Min AS Col  
  8. UNION ALL  
  9. SELECT COl+1 as t FROM CTE c  
  10. WHERE COl<@max)  
  11. 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.
Ebook Download
View all
Learn
View all