歯抜けのIDを全て求める

ここ最近のアクセスログを閲覧してみたところ、歯抜けのIDの求め方を探している方が多いことに気づいたので、自分でもちょっと考えてみた。

過去のエントリで紹介した方法は歯抜けの最小値を求めるもので、今回は指定した範囲の歯抜けを全て求めるものである。

今回の方法でもmin関数を使って歯抜けの最小値は求めることができる。

動作検証はMySQLで行なっているけれど特別な関数は全く使用していないので、他のデータベースでもほんの少し修正(Oracleの場合はfrom dualを追加するとか)するだけで動作すると思う。

作成したtablenameテーブルのIDカラムは数値型で、現在(2, 3, 5, 7, 11)が使用済みであり、歯抜け状態になっている。

mysql>  select id from tablename;
+----+
| id |
+----+
|  2 |
|  3 |
|  5 |
|  7 |
| 11 |
+----+
5 rows in set (0.05 sec)

このtablenameテーブルから使用されていないID(1, 4, 6, 8, 9, 10)を求めるSQL文は、以下のようになった。

mysql>  select id
    ->  from (
    ->  select (ones.num + tens.num + 1) id
    ->  from (
    ->  select 0 num union all
    ->  select 1 num union all
    ->  select 2 num union all
    ->  select 3 num union all
    ->  select 4 num union all
    ->  select 5 num union all
    ->  select 6 num union all
    ->  select 7 num union all
    ->  select 8 num union all
    ->  select 9 num) ones
    ->  cross join
    ->  (
    ->  select 0 num union all
    ->  select 10 num union all
    ->  select 20 num union all
    ->  select 30 num union all
    ->  select 40 num union all
    ->  select 50 num union all
    ->  select 60 num union all
    ->  select 70 num union all
    ->  select 80 num union all
    ->  select 90 num) tens
    ->  ) dummy
    ->  where dummy.id not in (select tablename.id from tablename)
    ->  and dummy.id < (select max(tablename.id) from tablename);
+----+
| id |
+----+
|  1 |
|  4 |
|  6 |
|  8 |
|  9 |
| 10 |
+----+
6 rows in set (0.05 sec)

自分が持っている知識で思いついたのは、上記の他にはexcept(差集合)演算を用いた方法(下記)。

けれどもMySQLではexcept(差集合)演算がまだサポートされていないので、以下のSQL文はOracleで動作検証。

Oracleでexcept(差集合)演算を行なうには、minus演算子を使う。

 select id
 from (
   select (ones.num + tens.num + 1) id
   from (
     select 0 num from dual union all
     select 1 num from dual union all
     select 2 num from dual union all
     select 3 num from dual union all
     select 4 num from dual union all
     select 5 num from dual union all
     select 6 num from dual union all
     select 7 num from dual union all
     select 8 num from dual union all
     select 9 num from dual
   ) ones
   cross join
   (
     select 0 num from dual union all
     select 10 num from dual union all
     select 20 num from dual union all
     select 30 num from dual union all
     select 40 num from dual union all
     select 50 num from dual union all
     select 60 num from dual union all
     select 70 num from dual union all
     select 80 num from dual union all
     select 90 num from dual
   ) tens
 ) dummy
 where dummy.id < (select max(tablename.id) from tablename)
 minus
 select id from tablename;

最初のSQL文との違いは、where節の条件のうち

    -> dummy.id not in (select tablename.id from tablename)

の条件文が削除されて、

 minus
 select id from tablename;

が追加される。

どちらにしても、もっとスマートで冴えた方法があると思う。きっと。

なので、ここで紹介した歯抜けのIDを求める方法は、数多くある方法のひとつ(いや、ふたつか)として参考にしていただければ幸いである。

ちなみに上記の最初に紹介したSQL文は、初めてのSQLの10章「結合」で紹介されているSQL文の応用である(書籍を奥から引っ張り出して確認しました)。

同書の内容はMySQLを基準に書かれているが、MySQL特有の機能に依存している部分は少なく、ところどころでOracleやSQL Serverとの差異や互換性を例に挙げてSQLの基本構文を紹介している(同書によれば、本文中のサンプルはOracleやSQL Serverでも動作するらしい)。

これからSQLを勉強する方や、データベーススペシャリストを勉強する方にも、興味があれば一読をお勧めする。

Amazon.co.jp： 初めてのSQL