Gaps in sequences

My table has one column of type integer. The data within that column is a simple sequence of numbers. If I have a number of rows with the values incrementing by one, what SQL could I use to determine if there is a skip in sequence?

For example, in my one column, I have 999 rows with the integer values of 1 to 1000, excluding 500. How can I return an SQL result that has one row with the value of 500?

The easiest way is to join the table to itself. Join each row to the one with the next id, by matching t1.id + 1 = t2.id. Use an outer join, and look for unmatched rows, i.e. where t2.id is null:

select t1.id + 1 as missing
  from onethousand t1
left outer
  join onethousand t2
    on t1.id + 1 = t2.id
 where t2.id is null      

  
missing
500
1001    

That's the result you wanted, except for the 1001, which is from the row with the highest id in the table. In other words, 500 is missing from within the sequence, and 1001 is the first number missing after the sequence. You could remove this with:

   and t1.id < (select max(id) from onethousand)

The t1.id + 1 = t2.id self-join method will actually only report the first number in a gap in the sequence. If you were to delete the 501 and 502 rows, the outer join query will still only report 500 as missing. When matching t1.id + 1 = t2.id, 500 matches 499, but 501, 502, and 503 never come up because 500, 501, and 502 are missing from the t1 side of the join. This is easily seen with this query:

select t1.id     as t1id
     , t1.id + 1 as t1idplus1
     , t2.id     as t2id
  from onethousand t1
left outer
  join onethousand t2
    on t1.id +1 = t2.id 
 where t1.id between 497 and 504
order by t1.id    

  
t1id  t1idplus1  t2id  
497   498        498 
498   499        499 
499   500        NULL 
503   504        504 
504   505        505 

If you needed to itemize all the missing numbers, you could do that with a left outer join from The integers table.