Find gaps in sequences in SQL tables

So, you have auto increment column (id) and and want to know if any records have been deleted. For start, you could not delete the records. Instead, use an additional Boolean column, name it 'deleted' and use it to mark them as deleted. And when selecting data, simply add 'where not deleted'. This way, you retain all the data and may choose to 'empty the trash' when you decide.

Finding duplicates is quite easy (which is not actually the issue here):

SELECT id, COUNT(*) FROM tablename GROUP BY id HAVING COUNT(*) > 1;

But in order to detect gaps in sequence ranges and detect how many records have been removed from a table, take a look at following query. It will ignore the gap at the beginning (0 to n) and at the end (n to seed) of the table:

SELECT START,STOP FROM (SELECT m.id+1 AS START,(SELECT MIN(id)-1 FROM tablename AS X WHERE x.id > m.id) AS STOP
FROM tablename AS m LEFT OUTER JOIN tablename AS r ON m.id = r.id -1 WHERE r.id IS NULL) AS X WHERE STOP IS NOT NULL

Avoiding subqueries:

SELECT l.id + 1 AS START, MIN(fr.id) - 1 AS STOP FROM tablename AS l LEFT OUTER JOIN tablename AS r ON l.id = r.id - 1 LEFT OUTER JOIN tablename AS fr ON l.id < fr.id WHERE r.id IS NULL AND fr.id IS NOT NULL GROUP BY l.id, r.id;

Of course, this is hardly, if at all, better. The < in the join condition makes the join essentially a CROSS JOIN