title: SQL: find missing IDs in a table
body: <p>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:</p>
  
  <pre><code> ID  | Value    | More fields...
  ---------------------------------
   2   | Cat      | ... 
   3   | Fish     | ...
   6   | Dog      | ...
   7   | Aardvark | ...
   9   | Owl      | ...
   10  | Pig      | ...
   11  | Badger   | ...
   15  | Mongoose | ...
   19  | Ferret   | ...
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> ID 
  ----
   1
   4
   5
   8
   12
   13
   14
   16
   17
   18
  </code></pre>
  
  <p>Notes:</p>
  
  <ol>
  <li>It is assumed that the initial first ID was 1</li>
  <li>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)</li>
  </ol>
  
  <p>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).</p>
  
  <p>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.</p>