更新履歴 [2019年11月04日]「同じテーブルで自己結合して階層扱いにする」を追加。

トップページ > 基礎知識 > SQL > 同じテーブルで自己結合して階層扱いにする

自己結合とは

データベースのテーブルを結合する場合は異なるテーブル同士を用いることが多いですが、同じテーブルを再帰的に結合することを"自己結合"と呼びます。
自己結合を行う場合も対象とするテーブルが同じだけで内部結合(INNER JOIN)や外部結合(OUTER/LEFT/RIGHT JOIN)を用います。
自己結合する場合の条件としてはテーブル内に結合できる ID や文字列が他のレコードにあることで、一つのテーブルで階層を表す場合に利用されます。

今回は下図のように組織の"部"と"課"を登録したテーブル「d_organization」を用意します。
このテーブルにはレコードのユニーク ID となる"oraganization_id"の他、親の ID を指定する"parent_id"があります。
例えば ID が 2 の「総務課」には親 ID に 1 が入っており、oraganization_id が 1 の「総務部」の配下にあることを意味しています。

組織テーブル

組織テーブル「d_organization」を階層表示するために次のような SQL で自己結合します。
今回は left outer join を利用しており、結合するテーブルも同じですが区別するために T1 と T2 と区別しています。

SELECT T1.organization_id, T1.parent_id, T1.name, T2.name FROM d_organization T1 left outer join d_organization T2 on T1.organization_id = T2.parent_id;

SQL を実行した結果が下図のようになります。
親 ID が存在する"総務課"はその親 ID の組織の右列に表示されます。
"第一営業課"と"第二営業課"のように同じ親 ID が存在する場合はそれぞれの行で表示されます。
親 ID が値が無い"総務部"や存在しない親 ID の"製造課"には NULL が表示されます。

自己結合の結果

上記の left outer join での自己結合では結合できた行と結合できなかった行の全てが表示されますが、次の SQL のように inner join を利用すると結合できた行だけが表示されます。

SELECT T1.organization_id, T1.parent_id, T1.name, T2.name FROM d_organization T1 inner join d_organization T2 on T1.organization_id = T2.parent_id;

結合できた行だけ表示する