Get minimum unused value in MySQL column

Question

I have a table with integer ID column. I would like to get the minimum unused value for this column. The query should find the first hole in table IDs and get the minimum value inside it. I'll try to explain it with some examples.

Example 1: no-holes table

In this case, I have a table without holes and query should simply get the minimum unused value: should get: 4

|id|
|1 |
|2 |
|3 |

Example 2: table with hole on top

In this case, we have a hole on top (missing value: 1). The query finds the hole and gets the minimum value inside it: should get 1.

|id|
|2 |
|3 |
|4 |

Also in this case, we have a hole on top, but we have more missing values inside it (missing values: 1 and 2). The query finds the hole and gets the minimum value inside it: should get 1.

|id|
|3 |
|4 |
|5 |

Example 3: table with hole in the middle

In this case, we have a hole in the middle (missing values: 2 and 3). The query finds the hole and gets the minimum value inside it: should get 2.

|id|
|1 |
|4 |
|5 |

Example 4: table with holes on top and in the middle

In this case, we have multiple holes: one on top (missing value: 1) and one in the middle (missing value: 3). The query finds the first hole and gets the minimum value inside it: should get 1.

|id|
|2 |
|4 |
|6 |

I've tried the solution proposed in this post, but it doesn't work as expected in my case. Any ideas?

Answer 1

SELECT min(unused) AS unused
FROM (
    SELECT MIN(t1.id)+1 as unused
    FROM yourTable AS t1
    WHERE NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.id = t1.id+1)
    UNION
    -- Special case for missing the first row
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT * FROM yourTable WHERE id = 1)
) AS subquery

Answer 2

A slightly different way to do it using a join rather than EXISTS:-

SELECT MIN(t1.id)
FROM 
(
    SELECT 1 AS id
    UNION ALL
    SELECT id + 1
    FROM yourTable
) t1
LEFT OUTER JOIN yourTable t2
ON t1.id = t2.id
WHERE t2.id IS NULL;

Down side of any solution using a sub query is that they are not likely to use any indexes