【Oracle】階層問い合わせの使い方完全ガイド｜START WITH・CONNECT BY・LEVEL・SYS_CONNECT_BY_PATH・CONNECT_BY_ROOTまで解説

CREATE TABLE dept_tree (
    id        NUMBER       PRIMARY KEY,
    name      VARCHAR2(50) NOT NULL,
    parent_id NUMBER
);

INSERT INTO dept_tree VALUES (1, '経営管理部',     NULL);
INSERT INTO dept_tree VALUES (2, '営業本部',       1);
INSERT INTO dept_tree VALUES (3, '開発本部',       1);
INSERT INTO dept_tree VALUES (4, '営業1部',        2);
INSERT INTO dept_tree VALUES (5, '営業2部',        2);
INSERT INTO dept_tree VALUES (6, 'インフラチーム', 3);
INSERT INTO dept_tree VALUES (7, 'アプリチーム',   3);
INSERT INTO dept_tree VALUES (8, 'モバイル班',     7);
COMMIT;

SELECT 列名, ...
FROM テーブル名
[WHERE 条件]
START WITH  開始条件          -- どのノードから展開を始めるか
CONNECT BY PRIOR 親キー列 = 子キー列;  -- 親子関係の定義

SELECT
    LEVEL,
    id,
    name,
    parent_id
FROM dept_tree
START WITH parent_id IS NULL       -- parent_idがNULLのレコード（ルート）から開始
CONNECT BY PRIOR id = parent_id;   -- 「親のid」＝「子のparent_id」の関係で展開

SELECT
    LEVEL,
    name
FROM dept_tree
START WITH id = 8                  -- 「モバイル班」（末端ノード）から開始
CONNECT BY PRIOR parent_id = id;   -- 「親のparent_id」＝「子のid」で逆方向に展開

SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 3, ' ') || name AS tree_view
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT
    LEVEL,
    name,
    SYS_CONNECT_BY_PATH(name, ' / ') AS full_path
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT
    LEVEL,
    name,
    CONNECT_BY_ROOT name AS root_dept
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT
    LEVEL,
    name,
    CONNECT_BY_ISLEAF AS is_leaf
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 3, ' ') || name AS tree_view
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY name;  -- 兄弟間のみ name 順。親子の階層は維持される

SELECT
    LEVEL,
    name,
    CONNECT_BY_ISCYCLE AS is_cycle   -- 1=循環検出, 0=正常
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR id = parent_id;

SELECT LEVEL, name
FROM dept_tree
WHERE name != '営業本部'       -- 「営業本部」の行だけ消えるが、配下の「営業1部」「営業2部」は残る
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT LEVEL, name
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
  AND name != '営業本部';    -- 「営業本部」とその配下全体（営業1部・営業2部）が除外される

SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 3, ' ') || name AS tree_view
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
  AND LEVEL <= 2;  -- 2階層まで展開（経営管理部・営業本部・開発本部のみ）

SELECT
    LEVEL - 1                          AS depth,
    name,
    CONNECT_BY_ISLEAF                  AS is_leaf,
    SYS_CONNECT_BY_PATH(name, ' > ')   AS full_path
FROM dept_tree
START WITH id = 3                -- 「開発本部」を起点
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id;

SELECT
    LEVEL AS steps_to_root,
    name,
    CONNECT_BY_ROOT name AS root_name
FROM dept_tree
START WITH id = 8                  -- 「モバイル班」から開始
CONNECT BY PRIOR parent_id = id    -- 逆方向（子→親）に辿る
ORDER BY LEVEL;

-- 「営業1部」（id=4）と同じルートに属する全ノードを取得する
SELECT name, LEVEL
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
HAVING CONNECT_BY_ROOT id = (
    SELECT CONNECT_BY_ROOT id
    FROM dept_tree
    START WITH id = 4
    CONNECT BY PRIOR parent_id = id
    WHERE parent_id IS NULL  -- ルートノードだけ取得
);

SELECT
    LEVEL,
    name,
    SYS_CONNECT_BY_PATH(name, ' / ') AS path
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

WITH tree (id, name, parent_id, lvl, path) AS (
    -- アンカー部分：ルートノードを取得
    SELECT
        id, name, parent_id,
        1 AS lvl,
        CAST(name AS VARCHAR2(500)) AS path
    FROM dept_tree
    WHERE parent_id IS NULL
    UNION ALL
    -- 再帰部分：子ノードを追加していく
    SELECT
        d.id, d.name, d.parent_id,
        t.lvl + 1,
        CAST(t.path || ' / ' || d.name AS VARCHAR2(500))
    FROM dept_tree d
    JOIN tree t ON d.parent_id = t.id
)
SELECT lvl AS "LEVEL", name, ' / ' || path AS path
FROM tree
ORDER BY path;

CREATE INDEX idx_dept_tree_parent
    ON dept_tree (parent_id);
-- CONNECT BY が parent_id を頻繁に参照するため、インデックスが効果的

EXPLAIN PLAN FOR
SELECT LEVEL, name
FROM dept_tree
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 「CONNECT BY WITH FILTERING」と表示されれば最適化されたアクセスパスが選ばれている