途中の空き番号（欠番）を取得するSQL（SELECT文）

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

1 空き番号を取得するテーブルの準備
- 1.1 STAFFテーブルの作成
- 1.2 STAFFテーブルのデータ挿入
2 空き番号を取得するSQL
3 途中の欠番の一覧を取得するSQL
- 3.1 SQLサンプル

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

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

STAFFテーブルの作成

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

CREATE TABLE STAFF(
    STAFF_NO int NOT NULL,
    STAFF_NAME nvarchar(50) NULL,
    CONSTRAINT [PK_STAFF] PRIMARY KEY CLUSTERED
    (
    STAFF_NO ASC
    )
)

CREATE TABLE STAFF(

STAFF_NO int NOT NULL,

STAFF_NAME nvarchar(50) NULL,

CONSTRAINT [PK_STAFF] PRIMARY KEY CLUSTERED

(

STAFF_NO ASC

)

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

CREATE TABLE STAFF(
    STAFF_NO int NOT NULL,
    STAFF_NAME nvarchar(50) NULL,
    PRIMARY KEY (STAFF_NO)
)

CREATE TABLE STAFF(

STAFF_NO int NOT NULL,

STAFF_NAME nvarchar(50) NULL,

PRIMARY KEY (STAFF_NO)

)

STAFFテーブルのデータ挿入

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

INSERT INTO STAFF
    (STAFF_NO, STAFF_NAME)
VALUES
    (1, 'スタッフ名1'),
    (3, 'スタッフ名2'),
    (6, 'スタッフ名3');

INSERT INTO STAFF

(STAFF_NO, STAFF_NAME)

VALUES

(1, 'スタッフ名1'),

(3, 'スタッフ名2'),

(6, 'スタッフ名3');

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

空き番号を取得するSQL

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

空き番号の取得

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

SELECT
    -- 現在登録されている番号に1を加算した番号
    MIN(STAFF_NO + 1) AS STAFF_NO
FROM
    STAFF
WHERE
    -- テーブルに存在しないかどうか
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

SELECT

-- 現在登録されている番号に1を加算した番号

MIN(STAFF_NO + 1) AS STAFF_NO

FROM

STAFF

WHERE

-- テーブルに存在しないかどうか

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

実行結果

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

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

-- STAFF_NOが2のレコードを挿入
INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (2, 'スタッフ名2');

SELECT
    MIN(STAFF_NO + 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

-- STAFF_NOが2のレコードを挿入

INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (2, 'スタッフ名2');

SELECT

MIN(STAFF_NO + 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

実行結果

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

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

-- STAFF_NOが4のレコードを挿入
INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (4, 'スタッフ名4');

SELECT
    MIN(STAFF_NO + 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

-- STAFF_NOが4のレコードを挿入

INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (4, 'スタッフ名4');

SELECT

MIN(STAFF_NO + 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

実行結果

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

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

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

-- STAFF_NOが5のレコードを挿入
INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (5, 'スタッフ名5');

SELECT
    MIN(STAFF_NO + 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

-- STAFF_NOが5のレコードを挿入

INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (5, 'スタッフ名5');

SELECT

MIN(STAFF_NO + 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

実行結果

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

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

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

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

SELECT
    -- ISNULL関数を使って初期番号「1」を指定
    ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

SELECT

-- ISNULL関数を使って初期番号「1」を指定

ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

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

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

SELECT
    -- IFNULL関数を使って初期番号「1」を指定
    IFNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

SELECT

-- IFNULL関数を使って初期番号「1」を指定

IFNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

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

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

-- 全件削除
DELETE STAFF;
-- STAFF_NOが2のレコードを挿入
INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (2, 'スタッフ名2');
-- 空き番号を取得
SELECT
    ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO
FROM
    STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

-- 全件削除

DELETE STAFF;

-- STAFF_NOが2のレコードを挿入

INSERT INTO STAFF (STAFF_NO, STAFF_NAME) VALUES (2, 'スタッフ名2');

-- 空き番号を取得

SELECT

ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO

FROM

STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

実行結果

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

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

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

SELECT
    ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO
FROM
    -- FROM句を変更
    (
    -- STAFFテーブルからSTAFF_NOを取得
    SELECT STAFF_NO FROM STAFF
    -- STAFF_NOの初期値として0をUNIONで結合
    UNION
    SELECT 0 WHERE (SELECT COUNT(STAFF_NO) FROM STAFF WHERE STAFF_NO = 1) = 0
    ) AS STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

SELECT

ISNULL(MIN(STAFF_NO + 1), 1) AS STAFF_NO

FROM

-- FROM句を変更

(

-- STAFFテーブルからSTAFF_NOを取得

SELECT STAFF_NO FROM STAFF

-- STAFF_NOの初期値として0をUNIONで結合

UNION

SELECT 0 WHERE (SELECT COUNT(STAFF_NO) FROM STAFF WHERE STAFF_NO = 1) = 0

) AS STAFF

WHERE

(STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)

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

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

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

SQLサンプル

SELECT
    ISNULL((STAFF_NO + 1), 1) AS STAFF_NO
FROM
    (
    SELECT STAFF_NO FROM STAFF
    UNION
    SELECT 0 WHERE (SELECT COUNT(STAFF_NO) FROM STAFF WHERE STAFF_NO = 1) = 0
    ) AS STAFF
WHERE
    (STAFF_NO + 1) NOT IN (SELECT STAFF_NO FROM STAFF)
-- WHERE句に条件を追加
AND
    -- 現在存在している最大の番号より小さいレコードのみ対象とする
    STAFF_NO < (SELECT MAX(STAFF_NO) FROM STAFF)