Finding Missing Values In A MySQL Table

19th September 2008

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:

  1. <p>Gets the following data:</p>
  2.  
  3. <pre language=">1
  4. 3
  5. 10
  6. 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. <p>Produces the following result.</p>
  2.  
  3. <pre language=" plain="">2
  4. 4
  5. 11
  6. 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. <p>This query gives the following result.</p>
  2.  
  3. <table><tr><th>Missing From</th><th>To</th></tr>
  4. <tr><td>2</td><td>2</td></tr>
  5. <tr><td>4</td><td>9</td></tr>
  6. <tr><td>11</td><td>22</td></tr></table>
  7.  
  8. <p>Using this dataset we can figure out where the gaps in the data are and perhaps do something with them.</p></body></html>">

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!

philipnorton42 (Fri, 09/19/2008 - 10:38)

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

philipnorton42 (Wed, 10/19/2011 - 21:08)

Add new comment

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