はじめに
Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、start with
句、connect by
句を扱います。
対象読者
- Oracleの階層問い合わせを使いたい方
- OracleのSQLの理解を深めたい方
階層問い合わせは、木構造の知識を必要としますので、『AVL木で木構造を学ぼう - @IT』や、『データ構造の選択次第で天国と地獄の差 - @IT』を、読まれてからの方が、理解しやすいと思います。
必要な環境
本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。
1. start with句とconnect by句
まずは、基本的な階層問い合わせを見てみましょう。
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句の段階でのイメージ start with句の段階でのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/2694/hierarchicalqueries1-1-1.gif)
そして、connect by
句で、親子関係の条件を指定します。prior ID = OyaID
を満たせば親子関係があると判定されます。
prior
演算子は、親の行の値であることを意味します。prior ID = OyaID
は、(親の行の)ID = (子の行の)OyaID
という意味になります。
connect by
句での、親子関係の条件を元に、木の根からのすべての子孫が行データとして返されます。そして、select
句でLevel
擬似列を指定して、ノードのレベルを表示してます。SQLのイメージは下記となります。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/2694/hierarchicalqueries1-1-2.gif)