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

You can create a table with just numbers in it. I'm simulating this table in below query. Then you can left join this table.

SELECT
MIN(numbers.n) AS missing_value
FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
LEFT JOIN your_table yt ON numbers.n = yt.id
WHERE yt.id IS NULL

Answer 3

If you have values from 1 to n in some other table say t2 then by simply checking

select min(id1) from t2 where id1 not exist(select id from t1);

you will get your answer;

Answer 4

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