Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hidesroom

Visualize SQL | Hierarchical and Recursive Query in SQL

Posted at

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
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

▼　Use previous result for excequting next query
rec_02.png

▼　When recursion ends
rec_03.png

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

【Recursion】Tree Structure | Preparation

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

Extract staff members under the parent(King)

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

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

let's see more detail・・・

Extract staff members under the parent(Jeff)

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

Step1：Retrieve Jeff's information

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
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

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
-- 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

Step3：Extract Kate's staff Bill(NOT recursive)

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

Step4：Bill has no staff. Recursion ends

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

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

That's all
Hope you understand recursive query

Reference site

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?