途中の空き番号(欠番)を取得するSQL

データのIDや番号などの連番を持ったテーブルの登録と削除を繰り返していると、途中に空き番号ができる場合があります。
新たにデータを登録する際に、現在登録されている番号の次の番号を登録するのではなく、途中の欠番を使用したい時が出てきます。
今回はそんな状況で使える途中の空き番号(歯抜けになった欠番)をSQLで取得する方法を紹介します。

空き番号を取得するテーブルの準備

空き番号を取得するためにテーブルを用意します。
テーブルはスタッフを管理するSTAFFテーブルとします。
STAFFテーブルにはSTAFF_NO列とSTAFF_NAME列を作成します。

STAFFテーブルの作成

SQLServerでのテーブルのCREATE文です。

MySQLでのテーブルのCREATE文です。

STAFFテーブルのデータ挿入

STAFFテーブルには以下SQLを実行してレコードを挿入します。

上記のSTAFFテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

空き番号を取得するSQL

空き番号を取得するSQLでは現在テーブルに登録されている番号(STAFF_NO)に1を加算した番号が、テーブルに存在しないかどうかを調べることによって取得します。
また、取得対象となる番号は空き番号の中で最小のものを有効な番号とします。

空き番号の取得

以下に上記の仕様を満たすSQLを記載します。

実行結果

STAFFテーブルのデータ挿入」ではSTAFF_NOが1と3と6のレコードをINSERTしているので、実行結果は2が取得されます。

空き番号取得SQL実行結果 STAFF_NO = 2を取得

次にSTAFF_NOが2のレコードをINSERTして再度実行してみます。

実行結果

今度はSTAFF_NOが2のレコードを登録したので次の空き番号の4が取得されます。

空き番号取得SQL実行結果 STAFF_NO = 4を取得

ついでにSTAFF_NOが4のレコードをINSERTして再度実行してみます。

実行結果

空き番号の5が取得されます。

空き番号取得SQL実行結果 STAFF_NO = 5を取得

途中の空き番号がない場合の結果

途中に空き番号(欠番)がない場合の取得結果はどうなるでしょうか。
STAFF_NOが5のレコードをINSERTして、SATFF_NOが1~6までのレコードを整備して実行してみます。

実行結果

途中に空き番号がないので現在テーブルに登録されている番号の最大 + 1の番号が取得されます。

空き番号取得SQL実行結果 STAFF_NO = 7を取得

テーブルにレコードが存在しない場合の対応

空き番号の取得」に記載したSQLでも悪くはないんですが、このままではレコードが1件も登録されていない場合にNULLが返ってきます。

空き番号取得SQL実行結果 未登録時にNULLを取得

ですので「MIN(STAFF_NO + 1)」の部分にISNULL関数を使い「ISNULL(MIN(STAFF_NO + 1), 初期番号)」とします。

ISNULL関数でNULLの場合に初期番号(上記の例では1)を指定することによって、テーブルにレコードの登録がない場合でも空き番号を取得することができます。

空き番号取得SQL実行結果 STAFF_NO = 1を取得

上記の例はSQLServerでのSQLになりますが、MySQLであればISNULL関数の代わりにIFNULL関数を使用します。

テーブルに番号: 1のレコードが存在しない場合の対応

ここまででSQLは完成のように思えますが、実は上記のSQLではSTAFF_IDが1のレコードがない場合に正しい空き番号(1)が取得できません。

実行結果

全レコードを削除してからSTAFF_NOが2のレコードを挿入して結果を取得してみます。

空き番号取得SQL実行結果 STAFF_NO = 3を取得

空き番号として3が取得されてしまいます。

初期番号(番号が1)のレコードが存在しない場合に取得できるようにするには、初期番号から1を引いたレコードを初期番号がない場合に取得するようにします。

上記のSQLではFROM句を変更して、STAFF_NOが1のレコードが存在しない場合にSTAFF_NOが0のレコードをUNIONで結合しています。(5行目~11行目)
こうすることによって、STAFF_NOが1のレコードが存在しない場合にはSTAFF_NOが0のレコードがMIN関数によって抽出され、結果として空き番号として1が取得できるようになります。

空き番号取得SQL実行結果 初期番号を取得を取得

途中の欠番の一覧を取得するSQL

最後に途中にある欠番(現在存在している最大の番号の次の番号を含まない空き番号)の一覧を取得するSQLのサンプルを記載しておきます。
途中にある欠番の一覧を取得するには、WHERE句に条件を追加し、テーブルに存在している最大の番号のレコードを取得結果に含まないようにします。

SQLサンプル

以上で空き番号の取得のSQLのご紹介は終わりです。
空き番号の取得はよく使う処理なので、SQLで作成できるようになっておくと、きっと役に立つ時が来ると思います。