はじめに
Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、start with句、connect by句を扱います。
対象読者
- Oracleの階層問い合わせを使いたい方
- OracleのSQLの理解を深めたい方
階層問い合わせは、木構造の知識を必要としますので、『AVL木で木構造を学ぼう － ＠IT』や、『データ構造の選択次第で天国と地獄の差 － ＠IT』を、読まれてからの方が、理解しやすいと思います。
必要な環境
本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。
1. start with句とconnect by句
まずは、基本的な階層問い合わせを見てみましょう。
IDTable
| ID | OyaID |
| 1 | null |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 1 |
| 6 | 5 |
| 7 | 2 |
| 20 | null |
| 21 | 20 |
| 22 | 21 |
基本的な階層問い合わせ
select ID,OyaID,Level from IDTable start with OyaID is null connect by prior ID = OyaID;
出力結果
| ID | OyaID | Level |
| 1 | null | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 3 | 4 |
| 7 | 2 | 3 |
| 5 | 1 | 2 |
| 6 | 5 | 3 |
| 20 | null | 1 |
| 21 | 20 | 2 |
| 22 | 21 | 3 |
解説すると、start with句で、木の根となる条件を指定します。OyaID is nullを満たすのは、IDが1の行と20の行です。start with句の段階での、SQLのイメージは下記となります。
start with句の段階でのイメージ
そして、connect by句で、親子関係の条件を指定します。prior ID = OyaIDを満たせば親子関係があると判定されます。
prior演算子は、親の行の値であることを意味します。prior ID = OyaIDは、（親の行の）ID = （子の行の）OyaID という意味になります。
connect by句での、親子関係の条件を元に、木の根からのすべての子孫が行データとして返されます。そして、select句でLevel擬似列を指定して、ノードのレベルを表示してます。SQLのイメージは下記となります。
SQLのイメージ
