Finding Missing Values In A MySQL Table

19th September 2008 - 3 minutes read time

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 FromTo
22
49
1122

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

MySQL

Comments

Nice Work! My MySql keeps pulling up an error on the last SQL statement. There error is: #1054 - Unknown column 't1.id' in 'having clause' Versions are: phpMyAdmin - 2.11.5.2 MySQL client version: 5.0.45 Any ideas?

Submitted by Jamie Bicknell on Fri, 09/19/2008 - 09:44

Permalink
Odd this... It works in MySQL query browser, so I thought it must be an issue with phpMyAdmin. And it was! For some reason it has a problem with the table.column reference in the having command. I found the solution was to add quotes around the name, like this:
HAVING 't1.ID' < MIN(t2.ID) - 1
} Let us know if that works!
Philip Norton

Submitted by philipnorton42 on Fri, 09/19/2008 - 10:38

Permalink
Great example! Thanks!

Submitted by Nalle on Sun, 02/01/2009 - 23:01

Permalink

Very nice. I tweaked your example a little to find records that are missing by date

CREATE  TABLE IF NOT EXISTS `sequence` (
 `id` BIGINT NOT NULL AUTO_INCREMENT ,
 `date` DATETIME NOT NULL ,
 PRIMARY KEY (`id`)
)
ENGINE = InnoDB;



# missing 10/2, 10/12, and 10/13

INSERT INTO sequence (date) values ('2011-10-01 00:00:00');
INSERT INTO sequence (date) values ('2011-10-03 00:00:00');
INSERT INTO sequence (date) values ('2011-10-04 00:00:00');
INSERT INTO sequence (date) values ('2011-10-05 00:00:00');
INSERT INTO sequence (date) values ('2011-10-06 00:00:00');
INSERT INTO sequence (date) values ('2011-10-07 00:00:00');
INSERT INTO sequence (date) values ('2011-10-08 00:00:00');
INSERT INTO sequence (date) values ('2011-10-09 00:00:00');
INSERT INTO sequence (date) values ('2011-10-10 00:00:00');
INSERT INTO sequence (date) values ('2011-10-11 00:00:00');
INSERT INTO sequence (date) values ('2011-10-14 00:00:00');
INSERT INTO sequence (date) values ('2011-10-15 00:00:00');
INSERT INTO sequence (date) values ('2011-10-16 00:00:00');
INSERT INTO sequence (date) values ('2011-10-17 00:00:00');
INSERT INTO sequence (date) values ('2011-10-18 00:00:00');
INSERT INTO sequence (date) values ('2011-10-19 00:00:00');
INSERT INTO sequence (date) values ('2011-10-20 00:00:00');


mysql&gt; SELECT
   -> DATE_ADD(t1.date, INTERVAL 1 DAY) AS 'Missing From',
   -> DATE_ADD( MIN(t2.date), INTERVAL -1 DAY) AS 'To'
   -> FROM sequence AS t1, sequence AS t2
   -> WHERE t1.date  GROUP BY t1.date
   -> HAVING t1.date < DATE_ADD( MIN(t2.date), INTERVAL -1 DAY);
+---------------------+---------------------+
| Missing From        | To                  |
+---------------------+---------------------+
| 2011-10-02 00:00:00 | 2011-10-02 00:00:00 |
| 2011-10-12 00:00:00 | 2011-10-13 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

Submitted by andy on Wed, 10/19/2011 - 21:07

Permalink

That's awesome! I'm sure I'll have a use for that one day. 

Thanks for your input Andy :)

Philip Norton

Submitted by philipnorton42 on Wed, 10/19/2011 - 21:08

Permalink

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
7 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Creating An Authentication System With PHP and MariaDB

15th October 2023

Using frameworks to handle the authentication of your PHP application is perfectly fine to do, and normally encouraged. They abstract away all of the complexity of managing users and sessions that need to work in order to allow your application to function.

Read more

Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Read more

Connecting To A Non Standard MySQL Socket In PHP

11th February 2011

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details.

Read more

Count Number Of Characters In A String With MySQL

30th September 2010

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL.

Read more

MySQL Order Table By Character Length

24th September 2009

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

Read more

Backup MySQL Database PHP Script

12th August 2009

There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.

Read more