SQL: find missing IDs in a table

Question

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.

Answer 1

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.