This question already has an answer here:
I have a huge table with a couple hundred thousand records. They all have a unique id for the first column though some are missing in the sequence of numbers.
I am trying to show a list of the numbers that are missing individually.
So for example:
10029
10032
10034
10036
I am trying to get it to show:
10030
10031
10033
10035
I found this query but it seems to be missing numbers when there's a range:
SELECT t1.id+1 AS Missing
FROM data AS t1
LEFT JOIN data AS t2
ON t1.id+1 = t2.id
WHERE t2.id IS NULL
Result:
10030
10033
As you can see, 10031 and 10035 are missing from that list.
</div>
Perhaps something like this as a starting point. Not tested, but might just work
SELECT @min := (SELECT min(id) FROM yourtable); // get the smallest ID in the table
SELECT @last := @min; // cache the min value for the where clause
SELECT id, id - @last AS difference, @last := id
FROM yourtable
WHERE id > @min
ORDER BY id ASC
HAVING difference > 1
it won't give you the individual missing ids, but it'll tell you where the gaps are, and how big they are.