SQLで欠番一覧を取得する [SQLServer, MySQL]

IDや通し番号などの一意になる番号を持つテーブルの登録や削除を繰り返していると、欠番（歯抜けの番号）ができる場合があります。
そんな時に客先の要望で、現時点での欠番（空き番号）の一覧が欲しいといわれることがあります。
そこで今回は、SQLでIDなどの一意に付けられた番号を持つテーブルの欠番の一覧を取得する方法を紹介します。

1 サンプルテーブルとデータの準備
2 連番データの取得
3 欠番一覧の取得

サンプルテーブルとデータの準備

欠番の一覧を取得するサンプルのテーブルの作成と初期データを投入します。

テーブルの作成 (CREATE)

-- 欠番取得用のテーブルを作成
CREATE TABLE NUMBER_TABLE
(
    NUMBER INT NOT NULL PRIMARY KEY
);

-- 欠番取得用のテーブルを作成

CREATE TABLE NUMBER_TABLE

(

NUMBER INT NOT NULL PRIMARY KEY

);

データの挿入 (INSERT)

-- 欠番のあるデータを挿入
INSERT INTO NUMBER_TABLE
    (NUMBER)
VALUES
    (2),
    (4),
    (5),
    (8),
    (9),
    (10),
    (14),
    (15);

-- 欠番のあるデータを挿入

INSERT INTO NUMBER_TABLE

(NUMBER)

VALUES

(2),

(4),

(5),

(8),

(9),

(10),

(14),

(15);

テーブルに登録する番号は1～15までとします。
そのうち欠番は「1, 3, 6, 7, 11, 12, 13」になります。

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

連番データの取得

まずは開始番号～終了番号までの連番を取得するSQLを作成します。
連番を作成するにはWITH句（共通テーブル式〔CTE: Common Table Expression〕）を利用して取得します。
以下にSQLServerでの連番を取得するSQLの例を示します。

-- 開始番号
DECLARE @START_NUMBER INT;
-- 開始番号に1を指定
SET @START_NUMBER = 1;
-- 終了番号
DECLARE @END_NUMBER INT
-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定
SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);
-- CTE: 共通テーブル式
WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (
    SELECT
        @START_NUMBER
    UNION ALL
    SELECT
        NUMBER_VALUE + 1
    FROM
        MISSING_NUMBER_TABLE
    WHERE
        NUMBER_VALUE < @END_NUMBER
)
SELECT
    NUMBER_VALUE
FROM
    MISSING_NUMBER_TABLE;

-- 開始番号

DECLARE @START_NUMBER INT;

-- 開始番号に1を指定

SET @START_NUMBER = 1;

-- 終了番号

DECLARE @END_NUMBER INT

-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定

SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);

-- CTE: 共通テーブル式

WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (

SELECT

@START_NUMBER

UNION ALL

SELECT

NUMBER_VALUE + 1

FROM

MISSING_NUMBER_TABLE

WHERE

NUMBER_VALUE < @END_NUMBER

)

SELECT

NUMBER_VALUE

FROM

MISSING_NUMBER_TABLE;

WITH句で共通テーブル式を利用して1からNUMBER_TABLEテーブルに登録されている最大のNUMBERの値（15）までの連番を取得します。
上記のSQLを実行すると1～15までの行が取得できます。

取得結果

欠番一覧の取得

前章の連番を取得するクエリを少し変更して、MISSING_NUMBER_TABLEのNUMBER_VALUEがNUMBER_TABLEテーブルに存在しないレコードのみ取得することで欠番の一覧が取得できます。

SQLServerで取得

-- 開始番号
DECLARE @START_NUMBER INT;
-- 開始番号に1を指定
SET @START_NUMBER = 1;
-- 終了番号
DECLARE @END_NUMBER INT
-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定
SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);
-- CTE: 共通テーブル式
WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (
    SELECT
        @START_NUMBER
    UNION ALL
    SELECT
        NUMBER_VALUE + 1
    FROM
        MISSING_NUMBER_TABLE
    WHERE
        NUMBER_VALUE < @END_NUMBER
)
SELECT
    NUMBER_VALUE
FROM
    MISSING_NUMBER_TABLE
-- NUMBER_TABLEテーブルに存在しない番号のみ取得する
WHERE
    NOT EXISTS (
        SELECT * FROM NUMBER_TABLE
        WHERE NUMBER = NUMBER_VALUE
    );

-- 開始番号

DECLARE @START_NUMBER INT;

-- 開始番号に1を指定

SET @START_NUMBER = 1;

-- 終了番号

DECLARE @END_NUMBER INT

-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定

SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);

-- CTE: 共通テーブル式

WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (

SELECT

@START_NUMBER

UNION ALL

SELECT

NUMBER_VALUE + 1

FROM

MISSING_NUMBER_TABLE

WHERE

NUMBER_VALUE < @END_NUMBER

)

SELECT

NUMBER_VALUE

FROM

MISSING_NUMBER_TABLE

-- NUMBER_TABLEテーブルに存在しない番号のみ取得する

WHERE

NOT EXISTS (

SELECT * FROM NUMBER_TABLE

WHERE NUMBER = NUMBER_VALUE

);

取得結果

MySQLで取得

MySQLでもWITH句（共通テーブル式）を利用すれば、SQLServerの場合と同様に欠番の一覧を取得することができます。

-- 開始番号に1を指定
SET @START_NUMBER = 1;
-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定
SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);
-- CTE: 共通テーブル式
WITH RECURSIVE MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (
    SELECT
        @START_NUMBER
    UNION ALL
    SELECT
        NUMBER_VALUE + 1
    FROM
        MISSING_NUMBER_TABLE
    WHERE
        NUMBER_VALUE < @END_NUMBER
)
SELECT
    NUMBER_VALUE
FROM
    MISSING_NUMBER_TABLE
-- NUMBER_TABLEテーブルに存在しない番号のみ取得する
WHERE
    NOT EXISTS (
        SELECT * FROM NUMBER_TABLE
        WHERE NUMBER = NUMBER_VALUE
    );

-- 開始番号に1を指定

SET @START_NUMBER = 1;

-- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定

SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE);

-- CTE: 共通テーブル式

WITH RECURSIVE MISSING_NUMBER_TABLE(NUMBER_VALUE) AS (

SELECT

@START_NUMBER

UNION ALL

SELECT

NUMBER_VALUE + 1

FROM

MISSING_NUMBER_TABLE

WHERE

NUMBER_VALUE < @END_NUMBER

)

SELECT

NUMBER_VALUE

FROM

MISSING_NUMBER_TABLE

-- NUMBER_TABLEテーブルに存在しない番号のみ取得する

WHERE

NOT EXISTS (

SELECT * FROM NUMBER_TABLE

WHERE NUMBER = NUMBER_VALUE

);

Oracleで取得

OracleであればWITH句を利用しなくてもLEVEL擬似列を利用して連番のデータを取得することができます。

SELECT
	*
FROM
	(SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL <= 15) X
WHERE
	NOT EXISTS (SELECT * FROM NUMBER_TABLE N WHERE X.NUM = N.NUMBER);

SELECT

FROM

(SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL <= 15) X

WHERE

NOT EXISTS (SELECT * FROM NUMBER_TABLE N WHERE X.NUM = N.NUMBER);