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_atFROM arrc_vouchers t1WHERE 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

This just worked for me to find the gaps in a table with more than 80k rows:

SELECT CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missingFROM ( SELECT  @rownum:=@rownum+1 AS expected,  IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got FROM  (SELECT @rownum:=0) AS a  JOIN YourTable  ORDER BY YourCol ) AS zWHERE z.got!=0;

Result:

+------------------+| missing          |+------------------+| 1 thru 99        || 666 thru 667     || 50000            || 66419 thru 66456 |+------------------+4 rows in set (0.06 sec)

Note that the order of columns expected and got is critical.

If you know that YourCol doesn't start at 1 and that doesn't matter, you can replace

(SELECT @rownum:=0) AS a

with

(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a

New result:

+------------------+| missing          |+------------------+| 666 thru 667     || 50000            || 66419 thru 66456 |+------------------+3 rows in set (0.06 sec)

If you need to perform some kind of shell script task on the missing IDs, you can also use this variant in order to directly produce an expression you can iterate over in bash.

SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missingFROM (  SELECT   @rownum:=@rownum+1 AS expected,   IF(@rownum=height, 0, @rownum:=height) AS got  FROM   (SELECT @rownum:=0) AS a   JOIN block   ORDER BY height  ) AS z WHERE z.got!=0;

This produces an output like so

$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

You can then copy and paste it into a for loop in a bash terminal to execute a command for every ID

for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do  echo $ID  # fill the gapsdone

It's the same thing as above, only that it's both readable and executable. By changing the "CONCAT" command above, syntax can be generated for other programming languages. Or maybe even SQL.

Quick and Dirty query that should do the trick:

SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetweenFROM  (SELECT a1.id AS a , MIN(a2.id) AS b FROM arrc_vouchers  AS a1LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.idWHERE a1.id <= 100GROUP BY a1.id) AS tabWHERE b > a + 1

This will give you a table showing the id that has ids missing above it, and next_id that exists, and how many are missing between...e.g.

 id  next_id  missing_inbetween 1        4                  268       70                  175       87                 11