SQL: find missing IDs in a table

I have table with a unique auto-incremental primary key. Over time, entries may be deleted from the table, so there are "holes" in this field's values. For example, table data may be as follows:

 ID  | Value    | More fields...
---------------------------------
 2   | Cat      | ... 
 3   | Fish     | ...
 6   | Dog      | ...
 7   | Aardvark | ...
 9   | Owl      | ...
 10  | Pig      | ...
 11  | Badger   | ...
 15  | Mongoose | ...
 19  | Ferret   | ...

I'm interested in a query that will return the list of missing IDs in the table. For the data above, the expected results are:

 ID 
----
 1
 4
 5
 8
 12
 13
 14
 16
 17
 18

Notes:

1. It is assumed that the initial first ID was 1
2. The maximum ID that should be examined is the final one, i.e. it's okay to assume that there were no additional entries after the current last one (see additional data on this point below)

A drawback of the above requirements is that the list will not return IDs that were created after ID 19 and that were deleted. I'm currently solving this case in code, because I hold the max ID created. However, if the query can take as a parameter MaxID, and also return those IDs between the current max and MaxID, that would be a nice "bonus" (but certainly not a must).

I'm currently working with MySQL, but consider moving to SQL Server, so I would like the query to fit both. Also, if you are using anything that can't run on SQLite, please mention it, thanks.

I landed on this page hoping to find a solution for SQLITE as this was the only answer I found when searching for this same question for SQLITE.

The final solution I found was from this article here Float Middle Blog - SQLITE answer

Hope it helps someone else out :-)

the simple solution being:

SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);

genius.

This question often comes up, and sadly, the most common (and most portable) answer is to create a temporary table to hold the IDs that should be there, and do a left join. The syntax is pretty similar between MySQL and SQL Server. The only real difference is the temporary tables syntax.

In MySQL:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table IDSeq

In SQL Server:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create table #IDSeq
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    #idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table #IDSeq

Here's the query for SQL Server:

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

Hope this is helpful.