MySQL get missing IDs from table

I have this table in MySQL, for example:

ID | Name
1  | Bob
4  | Adam
6  | Someguy

If you notice, there is no ID number (2, 3 and 5).

How can I write a query so that MySQL would answer the missing IDs only, in this case: "2,3,5" ?

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM testtable AS a, testtable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

Hope this link also helps http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

A more efficent query:

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM my_table t3 WHERE t3.id > t1.id) as gap_ends_at
FROM my_table t1
WHERE NOT EXISTS (SELECT t2.id FROM my_table t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

MySQL get missing IDs from table

Related

Recent Posts