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.