図でイメージするOracle DatabaseのSQL全集

第6回 階層問い合わせ
Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治(やまぎし けんじ)ACE

SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。

目次

今回のテーマ

今回は、下記のOracleのSQL文の評価順序においての、3番目のstart with句と4番目のconnect by句で主に使われる、階層問い合わせの使用例と、私のSQLのイメージを解説します。

1番目 from句
2番目 where句 (結合条件)
3番目 start with句
4番目 connect by句
5番目 where句 (行のフィルタ条件)
6番目 group by句
7番目 having句
8番目 model句
9番目 select句
10番目 union、minus、intersectなどの集合演算
11番目 order by句

動作確認環境

Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)

階層問い合わせとは

木構造やグラフ構造に対するselect文

階層問い合わせは、データ構造が木構造の場合や、有向グラフや無向グラフなどのグラフ構造の場合の、select文で主に使用されます。
木構造や深さ優先探索や幅優先探索や有向グラフや無向グラフに関する知識があると、階層問い合わせを理解しやすいです。

参考リソース

@IT AVL木で木構造を学ぼう
@IT データ構造の選択
グラフ理論 - Wikipedia
最強最速アルゴリズマー養成講座 --- 「探索」虎の巻

▲ ページTOPに戻る

start with句とconnect by句

木の根となる条件と親子条件

start with句では、木の根となる条件を指定し、connect by句では、ノード同士の親子条件を指定します。

create table BasicSample(ID primary key,OyaID) as
select  1,null from dual union all
select  2,   1 from dual union all
select  3,   1 from dual union all
select  4,   2 from dual union all
select  5,   3 from dual union all
select 10,null from dual union all
select 11,  10 from dual union all
select 12,  10 from dual;
-- 基本的な階層問い合わせ
select ID,OyaID,Level
  from BasicSample
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID  OyaID  Level
--  -----  -----
 1   null      1
 2      1      2
 4      2      3
 3      1      2
 5      3      3
10   null      1
11     10      2
12     10      2

階層問い合わせでは、最初に、start with句で木の根となる条件が判定されます。
OyaID is nullを満たすのは、IDが1の行と10の行です。
start with句の段階での、SQLのイメージは下記となります。

start with句の段階のイメージ

次に、connect by句で、ノード同士の親子条件が判定されます。
prior ID = OyaIDを満たせば、親子関係があると判定されます。

prior演算子は、親の行の値であることを意味します。
prior ID = OyaIDは、(親の行の)ID = (子の行の)OyaID という意味になります。

connect by句での、親子条件を元に、木の根からの全ての子孫が行データとして返されます。
connect by句の段階での、SQLのイメージは下記となります。

connect by句の段階のイメージ

▲ ページTOPに戻る

connect by句で複数条件

親子条件の指定で論理演算

start with句およびconnect by句では、andやorを使った論理演算を行うことができます。

create table ConnBySample(
ID  number,
Seq number,
primary key(ID,Seq));

insert into ConnBySample
select 111,5 from dual union all
select 111,6 from dual union all
select 111,7 from dual union all
select 222,5 from dual union all
select 333,5 from dual union all
select 333,6 from dual union all
select 333,8 from dual;

木の根となる条件を、Seq = 5の行とし、親子条件を、同じIDで、子のSeqが親より1大きいこととして、階層問い合わせを行います。

-- connect by句で複数条件
select ID,Seq,Level
  from ConnBySample
start with Seq = 5
connect by prior ID  = ID
       and prior Seq = Seq-1;
出力結果
 ID  Seq  Level
---  ---  -----
111    5      1
111    6      2
111    7      3
222    5      1
333    5      1
333    6      2

上記のSQLでは、start with句で、Seq = 5を木の根となる条件として指定してます。
start with句の段階での、SQLのイメージは下記となります。

start with句の段階のイメージ

そして、connect by句で指定した、下記の親子条件を満たせば親子関係があると判定されます。

-- 親子条件
    prior ID  = ID
and prior Seq = Seq-1

connect by句の段階での、SQLのイメージは下記となります。
connect by句にprior ID = IDがありますので、IDごとに区切る赤線をイメージしてます。

connect by句の段階のイメージ

参考までに、connect by句で、必ずFalseとなる条件を指定した階層問い合わせを実行してみます。
(木の根だけが表示されます)

-- connect by句でFalseを指定
select ID,Seq,Level
  from ConnBySample
start with Seq = 5
connect by 1=0;
出力結果
 ID  Seq  Level
---  ---  -----
111    5      1
222    5      1
333    5      1

▲ ページTOPに戻る

start with句の省略

全ての行を、木の根とする

start with句を省略して階層問い合わせを行うことができます。

create table staWithSample(Val primary key) as
select 5 from dual union all
select 6 from dual union all
select 7 from dual union all
select 8 from dual;
-- start with句の省略
select Val,Level,
sys_connect_by_path(to_char(Val),',') as Path
  from staWithSample
connect by prior Val = Val-1;
出力結果
Val  Level  Path
---  -----  --------
  5      1  ,5
  6      2  ,5,6
  7      3  ,5,6,7
  8      4  ,5,6,7,8
  6      1  ,6
  7      2  ,6,7
  8      3  ,6,7,8
  7      1  ,7
  8      2  ,7,8
  8      1  ,8

start with句が省略されると、全ての行が、木の根となります。
start with句の段階での、SQLのイメージは下記となります。

start with句の段階のイメージ

そして、connect by句で指定した、prior Val = Val-1を満たせば親子関係があると判定されます。
connect by句の段階での、SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。

connect by句の段階のイメージ

参考までに、start with句で、必ずFalseとなる条件を指定してみます。
(結果は空集合になります)

-- start with句でFalseを指定
select Val,Level,
sys_connect_by_path(to_char(Val),',') as Path
  from staWithSample
start with 1=0
connect by prior Val = Val-1;
出力結果
--------------------------------
レコードが選択されませんでした。

▲ ページTOPに戻る

Left Curve
図でイメージする
Oracle DatabaseのSQL全集
Right Curve