Recursive queries
Recursive queries are used to query hierarchical data.
A recursive common table expression (CTE) is a CTE that references itself.
By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the
complete result set.
Syntax: ---> view
create view view_name as <select_query>;
drop view view_name;
with temp_name(mgid,empid,empname) as( query)
WITH expression_name (column_list) mid,eid,name
AS
-- Anchor member
initial_query
UNION ALL
-- Recursive member that references expression_name.
recursive_query
-- references expression name
SELECT *
FROM expression_name
-->An initial query that returns the base result set of the CTE.
-->The initial query is called an anchor member.
-->A recursive query that references the common table expression, therefore, it is called the
recursive member.
-->The recursive member is union-ed with the anchor member using the UNION ALL
operator.
-->A termination condition specified in the recursive member that terminates the execution
of the recursive member.
The execution order of a recursive CTE is as follows:
-->First, execute the anchor member to form the base result set (R0), use this result for the
next iteration.
-->Second, execute the recursive member with the input result set from the previous
iteration (Ri-1)
and return a sub-result set (Ri) until the termination condition is met.
-->Third, combine all result sets R0, R1, … Rn using UNION ALL operator to produce the final
result set.
select id
from employee e
where e.id=4;
WITH EXPL (M_ID, E_ID, E_NAME) AS
SELECT ROOT.M_ID, ROOT.E_ID, ROOT.E_NAME
FROM MG_DETAILS, ROOT
WHERE ROOT.M_ID = 3
UNION ALL
SELECT CHILD.M_ID, CHILD.E_ID, CHILD.E_NAME
FROM EXPL PARENT, MG_DETAILS CHILD
WHERE PARENT.E_ID = CHILD.M_ID
SELECT DISTINCT M_ID, E_ID, E_NAME
FROM EXPL
ORDER BY M_ID, E_ID;
SELECT CHILD.M_ID, CHILD.E_ID, CHILD.E_NAME
FROM PARENT, MG_DETAILS CHILD
WHERE PARENT.E_ID = CHILD.M_ID
create table p1 as SELECT ROOT.M_ID, ROOT.E_ID, ROOT.E_NAME
FROM MG_DETAILS ROOT
WHERE ROOT.M_ID = 3;
create table p3 as SELECT CHILD.M_ID, CHILD.E_ID, CHILD.E_NAME
FROM P2, MG_DETAILS CHILD
WHERE P2.E_ID = CHILD.M_ID;
WITH expression_name (column_list)
AS
initial_query
UNION ALL
recursive_query
SELECT *
FROM expression_name
