Finding Missing Values In A MySQL Table

19th September 2008 - 2 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. 1
  2. 3
  3. 10
  4. 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.

  1. SELECT t1.id+1 AS Missing
  2. FROM TABLE AS t1
  3. LEFT JOIN TABLE AS t2 ON t1.id+1 = t2.id
  4. WHERE t2.id IS NULL
  5. ORDER BY t1.id;

Produces the following result.

  1. 2
  2. 4
  3. 11
  4. 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.

  1. SELECT
  2. t1.id+1 AS 'Missing From',
  3. MIN(t2.id) - 1 AS 'To'
  4. FROM TABLE AS t1, TABLE AS t2
  5. WHERE t1.id < t2.id
  6. GROUP BY t1.id
  7. 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.

Comments

Permalink
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?

Jamie Bicknell (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:
  1. HAVING 't1.ID' }
  2.  
  3. Let us know if that works!
Permalink
Great example! Thanks!

Nalle (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> 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 +---------------------+---------------------+
| 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)

andy (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 :)

Add new comment

The content of this field is kept private and will not be shown publicly.