可視化SQL | 再帰処理、階層構造SQLの動作を図で確認する

# 初めに
再帰・・と呼んでるので
同じ処理を繰り返し実行しているのでしょうが
他の言語と同じように、処理を追う事が容易ではありません

SQLでの再帰処理が実行される様子？？を
図を使って解説してみます

**CTEの使い方を理解されている前提**で
すすめさせていただきます

再帰処理については、多数記事が公開されているので
こちらと合わせて確認いただければとおもいます
　⇒　下にいくつかリンクを張っています

まずは、シンプルな例をみてみます

# 【再帰処理】0 から 25 まで『5』づつ加算
```SQL:SQL
WITH RECURSIVE cte_table AS (

    -- 初回だけ実行される
	SELECT 0 AS num

	UNION ALL

    -- ２回目以降～実行される
	SELECT num + 5 AS num
	FROM cte_table
	WHERE num < 25

)

SELECT * FROM cte_table;
```

▼出力結果
|num|
|---|
|0|
|5|
|10|
|15|
|20|
|25|

# 再帰処理の挙動
ここでは、ざっくりとした再帰処理の流れを
可視化してみます

再帰処理は２つにわかれています
下記２つのSQLの結果を **UNION ALL** で結合してます


　■ 初回だけ実行される処理
　　`SELECT 0 AS num`
　　　⇒ 初期データを入れてる・・感じです
　　　⇒ cte_table の num カラムに 0 を入れる
   
　■ ２回目以降～に実行される処理
　　`SELECT num + 5 AS num FROM cte_table WHERE num < 25`
　　　⇒ CTE の名称と同じcte_table をFROM句で使用
　　　⇒ **自分自身を呼び出してる**・・イメージ
　　　⇒ numに5 を加算する処理を
　　　　 numが25より小さい時は処理を続ける
　　　⇒ 再帰処理の終了条件を WHERE句に記述


▼　**2つ目の処理が再帰処理される**
![rec_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/04bf36c5-b8ce-29b3-b673-462d6b0fc73b.png)


▼　**前回処理した結果を持つデータを次に使用する**
![rec_3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4a5bc582-f441-3983-75eb-54a4c2240329.png)


▼　**再帰処理が終わる時**
![rec_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9ea97db1-b524-5cb3-79a3-38b689cb400e.png)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
少し複雑な処理を見てみます

# 【再帰処理】ツリー構造 | 準備
```SQL:SQL
-- テーブル定義
CREATE TABLE tbl_employee (
    emp_id INTEGER       -- 従業員ID
    ,employee_name TEXT  -- 従業員名
    ,manager_id INTEGER  -- 従業員の上司ID
);

-- データ登録
INSERT INTO tbl_employee VALUES (1, 'King', NULL);
INSERT INTO tbl_employee VALUES (33, 'Jeff', 1);
INSERT INTO tbl_employee VALUES (55, 'Nancy', 1);
INSERT INTO tbl_employee VALUES (88, 'Bob', 1);
INSERT INTO tbl_employee VALUES (222, 'Dan', 33);
INSERT INTO tbl_employee VALUES (666, 'Kate', 33);
INSERT INTO tbl_employee VALUES (4444, 'Bill', 666);

SELECT * FROM tbl_employee;
```
▼出力結果
|emp_id|employee_name|manager_id|
|---|---|---|
|1|King|NULL|
|33|Jeff|1|
|55|Nancy|1|
|88|Bob|1|
|222|Dan|33|
|666|Kate|33|
|4444|Bill|666|

▼テーブル構造イメージ
![rec_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/34d0972d-01b4-458c-051c-9b157b1ff1d8.png)


# 親(King)の配下に所属するスタッフを取出す
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- King の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 1
	
	UNION ALL

    -- King 以下に配置されてるスタッフを取出す
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM cte_table AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)

SELECT * FROM cte_table

```
▼出力結果
|employee_id|employee_name|manager_id|
|---|---|---|
|1|King|NULL|
|33|Jeff|1|
|55|Nancy|1|
|55|Bob|1|
|222|Dan|33|
|666|Kate|33|
|4444|Bill|666|

tbl_employee と同じ？？　確かにそうなりますね
Kingは一番TOPの人(`root`)なので、
その人物の配下のスタッフは、全員が対象です


**出力結果とtbl_employeeを比較**
![rec_9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/15b7844c-ec6d-e20e-8645-c60491aea413.png)

> tbl_employee をSELECTしているのと何が違うの？？
> FROM句でcte_tableを使ってるけど再帰処理してるの　(๑• •๑)??

こんな風に考えてしまいます・・が、再帰処理してます

▼　**こんな感じですが、動作がまだ見えません(￣Д￣;)**
![rec_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/baa2c625-651c-82bb-58d8-5cbe561e5ae5.png)

もう少し、細かくみてみます・・・

# 親(Jeff)の配下に所属するスタッフを取出す
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- 修正
	
	UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM cte_table AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table
```

▼出力結果
|employee_id|employee_name|manager_id|
|---|---|---|
|33|Jeff|1|
|222|Dan|33|
|666|Kate|33|
|4444|Bill|666|

▼　**Jeffの配下に所属するスタッフの取出しイメージ**

![rec_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c52a0de8-4f0a-46a8-887a-1eed44337439.png)


# Step1：Jeff を取出す
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- 修正
	
 -- UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
 -- SELECT emp.emp_id,emp.emp_name, emp.manager_id 
 -- FROM cte_table AS cte
 -- INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table
```
▼ 出力結果 | Jeffが取出せた
|employee_id|employee_name|manager_id|
|---|---|---|
|33|Jeff|1|

# Step2：Jeffの部下を取出す
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff の情報
 -- SELECT emp_id, emp_name, manager_id 
 -- FROM tbl_employee WHERE emp_id = 33 -- 修正
	
 -- UNION ALL

    -- Jeff 以下に配置されてるスタッフを取出す
    SELECT emp.emp_id,emp.emp_name, emp.manager_id 
    FROM cte_table AS cte
    INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	
)
SELECT * FROM cte_table
```

▼ 出力結果 エラーになる
![rec_14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a93e3352-266f-800d-9bd3-7c080d630eaf.png)

再帰処理の部分だけを実行したかった・・が
記述ルールに反している・・のでエラーになる
この部分を、再帰処理を使わずに書換えてみる
　⇒　`Step2：Jeffの部下を取出す`を書換え
 
# Step2：Jeffの部下、Dan,Kate を取出す(非再帰)
```SQL:SQL
-- Dan,Kate の情報
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
    -- Jeff の情報
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
```

▼ 出力結果　Dan, Kate 取出せた
|employee_id|employee_name|manager_id|
|---|---|---|
|222|Dan|33|
|666|Kate|33|

**動作の詳細**
　⇒ Jeffを上司に持つスタッフ(Dan,Kate)を取出すには
　　 最初に取得したJeffの情報に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得したJeffの情報が保持されてる
　⇒ 上司であるJeffのID(`cte.emp_id`:33) と
　　 同じ`manager_id`を持つ行をtbl_employeeから取出

▼ **Jeffの部下、Dane, Kate を取出すイメージ**
![rec_18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/08b62965-3394-632a-6768-4f67c725c2c0.png)



# Step3：Kateの部下、Bill を取出す(非再帰)
```SQL:SQL
-- Bill の情報
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM(
    -- Dan,Kate の情報
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM (
        -- Jeff の情報
		SELECT emp_id, emp_name, manager_id 
		FROM tbl_employee WHERE emp_id = 33
	) AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
```

▼ 出力結果 Bill が 取出せた
|employee_id|employee_name|manager_id|
|---|---|---|
|4444|Bill|666|

**動作の詳細**
　⇒ 前回取得した情報(Dan,Kate)に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得した情報が保持されてる
　⇒ 上司であるDan,KateのID(`cte.emp_id`:222,666) と
　　 同じ`manager_id`を持つ行をtbl_employeeから取出

▼ **Kateの部下、Billを取出すイメージ**
![rec_21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1e4d4453-7d1b-a06e-d642-415216ad45b3.png)



# Step4：Bill の部下がいない。再帰処理が終わる
```SQL:SQL
-- Bill の部下
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
	-- Bill の情報
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM(
		-- Dan,Kate の情報
		SELECT emp.emp_id,emp.emp_name, emp.manager_id 
		FROM (
			-- Jeff の情報
			SELECT emp_id, emp_name, manager_id 
			FROM tbl_employee WHERE emp_id = 33
		) AS cte
		INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
	) AS cte
	INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
) AS cte
INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id
```

▼ 出力結果 データ無い
|employee_id|employee_name|manager_id|
|---|---|---|
|　|　|　|

**動作の詳細**
　⇒ 前回取得した情報(Bill)に対して
　　 tbl_employee を join してます
　⇒ cte_table には前回取得した情報が保持されてる
　⇒ 上司であるBillのID(`cte.emp_id`:4444) と
　　 同じ`manager_id`を持つ行をtbl_employeeから取出

▼ **Billの部下を取出そうとするが、存在しない**
![rec_23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a71a97be-983c-c0f1-5ec4-209bb1aac42b.png)



### 戻り値が無いので、再帰処理が終了
Bill を上司に持つスタッフが存在しない
　⇒ SQL の結果が返ってこない
　⇒ ここで、再帰処理が終わる
　⇒ Step4 が最後の処理

> 最初の例では、再帰処理が終わる条件を
> WHERE句に記述していました
> 　⇒　WHERE num < 25
> この再帰クエリでは、『結果が返ってこない』
> を条件にして再帰処理が終ります



# 『処理の流れ』と『全体のイメージ図』
Step1 : Jeff の情報を取得
　`UNION ALL`
Step2 : Dan, Kate の情報を取得
　`UNION ALL`
Step3 : Bill の情報を取得

Step4 : 情報がないので終了


> 【再帰処理クエリ】↓↓↓
> SELECT emp.emp_id,emp.emp_name, emp.manager_id 
> FROM cte_table AS cte
> INNER JOIN tbl_employee AS emp ON emp.manager_id = cte.emp_id

> 再帰処理されるクエリは
> Step2, Step3, Step4 で実行されている
> Step4 で結果が返ってこないので
> 全体の処理がここで終了する

以上となります
再帰クエリがなんとなく理解いただければ幸いです

▼ **全体のイメージ図**
![rec_25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/42ff4d88-c820-d2c8-3bd5-ee2e6466b3f5.png)

# 参考サイト
https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

https://cosol.jp/techdb/2022/12/with_recursive_sql_xmas_tree/

https://sql-marketing-academy.subroq.co.jp/blogs/temp-tables-cte