Finding Missing Values In A MySQL Table

 If you have a table of incremental values it can be hard to find out which ones are missing. The only solution might be to write a script to get all the data from the database and see which ones are missing. However, there is a way of doing this without using a script.

Using a standard select query like this:

SELECT * FROM table;

Gets the following data:

1
3
10
23

We can see that values are missing, but which ones? The following query will show us where the gaps are in the data of the table.

SELECT t1.id+1 as Missing
FROM table as t1
LEFT JOIN table  as t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id;

Produces the following result.

2
4
11
24

However, this only tell us where the gaps are, not how long they are. To get the range of where the gaps from and to we need to do something a little more complex.

SELECT
t1.id+1 AS 'Missing From',
MIN(t2.id) - 1 AS 'To'
FROM table AS t1, table AS t2
WHERE t1.id < t2.id
GROUP BY t1.id
HAVING t1.id < MIN(t2.id) - 1;

This query gives the following result.

Missing From	To
2	2
4	9
11	22

Using this dataset we can figure out where the gaps in the data are and perhaps do something with them.