Visualize SQL | Hierarchical and Recursive Query in SQL

# Beginning
It's probably running the same process
over and over again ・・
Like other languages such as C++, Java
it is not easy to follow whole process

I explain how recursive processing is executed
using images

**You should know how to use CTE**
to read this article

There are many articles published 
about recursion processing, 
so I hope you can check this as well
　⇒ I put some links below


First, let's look at a simple example

# 【Recursion】Add by 5 from 0 to 25
```SQL:SQL
WITH RECURSIVE cte_table AS (

    -- Execute only the first time
	SELECT 0 AS num

	UNION ALL

    -- Executed from second time ～
	SELECT num + 5 AS num
	FROM cte_table
	WHERE num < 25

)

SELECT * FROM cte_table;
```

▼ Output
|num|
|---|
|0|
|5|
|10|
|15|
|20|
|25|

# Behavior of Recursion
I will briefly explain the flow of recursion processing
and visualize it

Recursion processing consists of two queries.
The results of the following two queries
are combined using **UNION ALL**

　■ Execute only the first time
　　`SELECT 0 AS num`
　　　⇒ It's like creating the initial data
　　　⇒ Put 0 in the num column of cte_table

　■ Executed from second time ～
　　`SELECT num + 5 AS num FROM cte_table WHERE num < 25`
　　　⇒ Use cte_table that is the same name as the CTE,
　　　　 in the FROM clause
　　　⇒ It's like **calling myself**
　　　⇒ Continue to add 5 to num
　　　　 while num is less than 25
　　　⇒ Write terminate condition of recursion
　　　　 in the WHERE clause


▼　**The second query is recursive**
![rec_01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/84c88a63-8e4a-fe78-3bfd-bb61d8a676fe.png)


▼　**Use previous result for excequting next query**
![rec_02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5e8c8f55-5478-ea78-6476-f7293ebaac1d.png)


▼　**When recursion ends**
![rec_03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ff1a759c-fcf1-0238-3658-18ca0d7352b7.png)


～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
Let's look at a slightly more complicated process


# 【Recursion】Tree Structure | Preparation
```SQL:SQL
-- Table Definition
CREATE TABLE tbl_employee (
    emp_id INTEGER       -- Employee ID
    ,employee_name TEXT  -- Employee Name
    ,manager_id INTEGER  -- Employee's boss ID
);

-- INSERT data
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;
```
▼ Output
|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|

▼ Table Structure Image
![rec_04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/69b08dc6-d080-9464-b0e5-6abf21ef3e71.png)



# Extract staff members under the parent(King)
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Information of King
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 1
	
	UNION ALL

    -- Extract staff members under the parent(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

```
▼ Output
|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|


Is it the same as tbl_employee(table)? 
・・well
King is the TOP person(`root`), so
everyone will be under him

**Compare query output and tbl_employee**
![rec_05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0846a29d-a2ff-0993-6bb5-2bd45f532218.png)


> What is the differenct from "select * from tbl_employee"?
> Is this query recursive　(๑• •๑)??

you might think this way・・but Yes!!
This query is recursive process


▼　**it's working like this but its process is unclear(￣Д￣;)**
![rec_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/baa2c625-651c-82bb-58d8-5cbe561e5ae5.png)

let's see more detail・・・

# Extract staff members under the parent(Jeff)
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff's information
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- modified
	
	UNION ALL

    -- Extract staff members under the parent(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
```

▼ Output
|employee_id|employee_name|manager_id|
|---|---|---|
|33|Jeff|1|
|222|Dan|33|
|666|Kate|33|
|4444|Bill|666|

▼　**Image of extracting staff under Jeff**
![rec_14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bcc2aa85-2264-60e6-1022-5e3f10f4b433.png)


# Step1：Retrieve Jeff's information
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff's information
	SELECT emp_id, emp_name, manager_id 
	FROM tbl_employee WHERE emp_id = 33 -- 修正
	
 -- UNION ALL

    -- Extract staff members under the parent(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
```
▼ Output | we got Jeff
|employee_id|employee_name|manager_id|
|---|---|---|
|33|Jeff|1|

# Step2：Extract staff under Jeff
```SQL:SQL
WITH RECURSIVE cte_table AS (
    -- Jeff's information
 -- SELECT emp_id, emp_name, manager_id 
 -- FROM tbl_employee WHERE emp_id = 33 -- modifi
	
 -- UNION ALL

    -- Extract staff members under the parent(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
```

▼ Output is Error!!
![rec_14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a93e3352-266f-800d-9bd3-7c080d630eaf.png)


I wanted to perform only recursive part, but
an error occurs because it is not right query for recursion
Write another query without using recursion
　⇒　rewrite `Step2：Extract staff under Jeff`

# Step2：Extract Jeff's staff Dan & Kate(NOT recursive)
```SQL:SQL
-- Dan,Kate's Information
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
    -- Jeff's information
	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
```

▼ Output | we got Dan, Kate
|employee_id|employee_name|manager_id|
|---|---|---|
|222|Dan|33|
|666|Kate|33|

**detail process**
　⇒ To extract staff(Dan,Kate) whose boss is Jeff is
　　 join tbl_employee table to Jeff's information
　　 that is taken at the beginning
　⇒ cte_table holds Jeff's information
　⇒ Extract the row with the same `manager_id`
　　 as emp_id(`33`) of Jeff from tbl_employee table


▼ **Image of extracting Jeff's staff(Dan & Kate)**
![rec_07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/52931723-23c7-3c9f-d3e1-87a5cde361ff.png)


# Step3：Extract Kate's staff Bill(NOT recursive)
```SQL:SQL
-- Bill's information
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM(
    -- Dan,Kate's information
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM (
        -- Jeff's information
		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
```

▼ Output | we got Bill
|employee_id|employee_name|manager_id|
|---|---|---|
|4444|Bill|666|

**detail process**
　⇒ join tbl_employee table to previous result
　　 that is Dan, Kate's info
　⇒ cte_table holds Dan, Kate's info
　⇒ Extract the row with the same `manager_id`
　　 as emp_id(`222,666`) of Dan,Kate from tbl_employee table

▼ **Image of extracting Kate's staff(Bill)**
![rec_09.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5bf7c499-30cc-11d2-a143-491643977cca.png)


# Step4：Bill has no staff. Recursion ends
```SQL:SQL
-- Bill's staff
SELECT emp.emp_id,emp.emp_name, emp.manager_id 
FROM (
	-- Bill's information
	SELECT emp.emp_id,emp.emp_name, emp.manager_id 
	FROM(
		-- Dan,Kate's information
		SELECT emp.emp_id,emp.emp_name, emp.manager_id 
		FROM (
			-- Jeff's information
			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
```

▼ Output no result
|employee_id|employee_name|manager_id|
|---|---|---|
|　|　|　|

**detail process**
　⇒ join tbl_employee table to previous result
　　 that is Bill's info
　⇒ cte_table holds Bill's info
　⇒ Extract the row with the same `manager_id`
　　 as emp_id(`4444`) of Bill from tbl_employee table



▼ **Try to extract Bill's staff but does not exist**
![rec_11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b9557e3b-e941-b6b6-c6ec-527fdc2a4ae6.png)




### no return value ⇒ recursion ends
There are no staff whose boss is Bill
　⇒ No return from query
　⇒ Recursion ends here
　⇒ Step4 is the last process

> In the first example
> the condition for ending recursion 
> is written in the WHERE clause
> 　⇒　WHERE num < 25
> In this recursive query 
> the recursive process ends on the condition 
> that no results are returned



# 『Process flow』&『whole image of recursion』
Step1 : Extract Jeff's information
　`UNION ALL`
Step2 : Extract Dan, Kate's information
　`UNION ALL`
Step3 : Extract Bill's information

Step4 : Recursion ends because no return value


> 【Recursive Query】
> 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


> Recursive query is executed in Step2, Step3, Step4
> Since no results are returned in Step 4
> the entire process ends here



▼ **whole image of recursion**
![rec_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ce94ead7-9a1a-813c-42b1-274d9cfbf3eb.png)

That's all
Hope you understand recursive query




# Reference site
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