Argthtjtr

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

To add a little to Ivan's answer, this version shows numbers missing at the beginning if 1 doesn't exist:

SELECT 1 as gap_starts_at,

       (SELECT MIN(t4.id) -1 FROM testtable t4 WHERE t4.id > 1) as gap_ends_at

FROM testtable t5

WHERE NOT EXISTS (SELECT t6.id FROM testtable t6 WHERE t6.id = 1)

HAVING gap_ends_at IS NOT NULL limit 1

UNION

SELECT (t1.id + 1) as gap_starts_at, 

       (SELECT MIN(t3.id) -1 FROM testtable t3 WHERE t3.id > t1.id) as gap_ends_at

FROM testtable t1

WHERE NOT EXISTS (SELECT t2.id FROM testtable t2 WHERE t2.id = t1.id + 1)

HAVING gap_ends_at IS NOT NULL;

Above queries will give two columns so you can try this to get the missing numbers in a single column

select start from 

(SELECT a.id+1 AS start, MIN(b.id) - 1 AS end

    FROM sequence AS a, sequence AS b

    WHERE a.id < b.id

    GROUP BY a.id

    HAVING start < MIN(b.id)) b

UNION

select c.end from (SELECT a.id+1 AS start, MIN(b.id) - 1 AS end

    FROM sequence AS a, sequence AS b

    WHERE a.id < b.id

    GROUP BY a.id

    HAVING start < MIN(b.id)) c order by start;

It would be far more efficient to get the start of the gap in one query and the end of the gap in one query.

I had 18M records and it took me less than a second each to get the two results. When I tried getting them together my query timed out after an hour.

Get the start of gap:

SELECT (t1.id + 1) as MissingID

FROM sequence t1

WHERE NOT EXISTS 

    (SELECT t2.id 

    FROM sequence t2 

    WHERE t2.id = t1.id + 1);

Get the end of gap:

SELECT (t1.id - 1) as MissingID

FROM sequence t1

WHERE NOT EXISTS 

    (SELECT t2.id 

    FROM sequence t2 

    WHERE t2.id = t1.id - 1);    

Rather than returning multiple ranges of IDs, if you instead want to retrieve every single missing ID itself, each one on its own row, you could do the following:

SELECT id+1 FROM table WHERE id NOT IN (SELECT id-1 FROM table) ORDER BY 1

The query is very efficient. However, it also includes one extra row on the end, which is equal to the highest ID number, plus 1. This last row can be ignored in your server script, by checking for the number of rows returned (mysqli_num_rows), and then using a for loop if the number of rows is greater than 1 (the query will always return at least one row).