sql - How to find gaps in sequential numbering in mysql?

Question

We have a database with a table whose values were imported from another system. There is an auto-increment column, and there are no duplicate values, but there are missing values. For example, running this query:

select count(id) from arrc_vouchers where id between 1 and 100

should return 100, but it returns 87 instead. Is there any query I can run that will return the values of the missing numbers? For example, the records may exist for id 1-70 and 83-100, but there are no records with id's of 71-82. I want to return 71, 72, 73, etc.

Is this possible?

Answer 1

Update

ConfexianMJS provided much better answer in terms of performance.

The (not as fast as possible) answer

Here's version that works on table of any size (not just on 100 rows):

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

• gap_starts_at - first id in current gap
• gap_ends_at - last id in current gap