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 ids of 71-82. I want to return 71, 72, 73, etc.

Is this possible?

Answer 1

This query will work 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