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.