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

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

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

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

テーブルの作成 (CREATE)

データの挿入 (INSERT)

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

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

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

連番データの取得

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

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

取得結果

WITH句で連番を取得

欠番一覧の取得

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

SQLServerで取得

取得結果

欠番一覧を取得

MySQLで取得

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

Oracleで取得

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