はじめに
Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、connect_by_IsLeaf疑似列、connect_by_root演算子、prior演算子を扱います。
対象読者
- Oracleの階層問い合わせを使いたい方
- OracleのSQLの理解を深めたい方
必要な環境
本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。
1. connect_by_IsLeaf疑似列
connect_by_IsLeaf疑似列は、そのノードが木の葉であれば1、木の葉でなければ0となります。サンプルを見てみましょう。
IsLeafT
| ID | OyaID |
| 1 | null |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 4 |
| 6 | 4 |
| 7 | 1 |
| 10 | null |
| 20 | null |
| 21 | 20 |
| 22 | 21 |
connect_by_IsLeafの使用例
select ID,OyaID,Level,connect_by_IsLeaf as isLeaf, sys_connect_by_path(to_char(ID),',') as path from IsLeafT start with OyaID is null connect by prior ID = OyaID;
出力結果
| ID | OyaID | Level | isLeaf | path |
| 1 | null | 1 | 0 | ,1 |
| 2 | 1 | 2 | 0 | ,1,2 |
| 3 | 2 | 3 | 1 | ,1,2,3 |
| 4 | 1 | 2 | 0 | ,1,4 |
| 5 | 4 | 3 | 1 | ,1,4,5 |
| 6 | 4 | 3 | 1 | ,1,4,6 |
| 7 | 1 | 2 | 1 | ,1,7 |
| 10 | null | 1 | 1 | ,10 |
| 20 | null | 1 | 0 | ,20 |
| 21 | 20 | 2 | 0 | ,20,21 |
| 22 | 21 | 3 | 1 | ,20,21,22 |
connect_by_IsLeaf疑似列のイメージは、下記となります。木ごとに区切る赤線をイメージして、葉であるノードに緑色を塗ってます。
connect_by_IsLeaf擬似列のイメージ
connect_by_IsLeaf疑似列の使用例としては、下記のSQLのように、where句で、connect_by_IsLeaf = 1を指定して、木の葉である行のみを取得することが多いです。
木の葉である行のみを取得
select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as path from IsLeafT where connect_by_IsLeaf = 1 start with OyaID is null connect by prior ID = OyaID;
出力結果
| ID | OyaID | Level | path |
| 3 | 2 | 3 | ,1,2,3 |
| 5 | 4 | 3 | ,1,4,5 |
| 6 | 4 | 3 | ,1,4,6 |
| 7 | 1 | 2 | ,1,7 |
| 10 | null | 1 | ,10 |
| 22 | 21 | 3 | ,20,21,22 |
