sql query to get deleted records

mysql sql select gaps-and-islands
Haradzieniec picture Haradzieniec · Jun 19, 2012 · Viewed 8.4k times · Source

You have a table table1 that contains id column, that is int(11), not null, auto_increment and starts from 1.

Suppose, you have 10,000 records. It is clear the id of the last record is 10,000. Once you removed 3 records, you have 9,997 records in the table, but the last record id value is still 10,000 (if the last record was not deleted).

How to display what records have been removed using 1 sql query?

Thank you.

Answer

sam yi picture sam yi · Jun 19, 2012

I think easiest would be to have a dummy/temp table with just ids. 1-1000 then left join to that table.

But be sure to remove the "deleted" records from your dummy/temp table once you're done. Otherwise, they will show up every time.

>> EDIT << You can do self join to figure out if you're missing ids....

select a.id + 1 MissingIds
from <table> a
left join <table> b
  on a.id = b.id - 1
where b.id is null
  and a.id < 10000

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More