sql query to get deleted records

Question

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 1

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