This article gives tips and tricks on how to write and understand hierarchical query in Oracle databases implemented using CONNECT BY
clause and then how to convert using recursive WITH
clause.
Hierarchical query using CONNECT BY
Oracle database provides CONNECT BY
, START WITH
clauses to operate on hierarchical data. LEVEL clause in Oracle database helps you build dynamic hierarchy and also gives you the depth of hierarchy. LEVEL
clause is very important consideration to generate “N” number of rows for a certain condition. You can learn in depth of hierarchical query in our documentation here.
Hierarchical query using Recursive Subquery Factoring
The “Recursive Subquery Factoring” is also referred as recursive WITH
clause. This functionality was introduced in Oracle 11g Release 2, giving an alternative to the method of CONNECT BY
hierarchical querying.
A recursive subquery factoring clause must contain two SELECT
query blocks combined by a UNION ALL
set operator within a WITH
clause.
- The first block is known as the anchor member, which can not reference the query name. It can be made up of one or more query blocks combined by the
UNION ALL
,UNION
,INTERSECT
orMINUS
set operators. - The second query block is known as the recursive member, which must reference the query name at-least once.
CONNECT BY hierarchical queries to recursive WITH clause recursive subquery factoring examples in Oracle
Here are some examples of using recursive subquery factoring to understand how they work in Oracle database.
Q : Generate numbers from 1 to 10.
Ans:
SELECT LEVEL AS num
FROM DUAL
CONNECT BY LEVEL <= 10;
WITH cte(n) AS(
--Anchor query part; fixed query; can not reference CTE
SELECT 1 AS n FROM DUAL
UNION ALL
--Recursive part; Must reference CTE at-least once
SELECT n+1 FROM cte
WHERE n+1 <= 10
)
SELECT n AS num FROM cte;
Q : Display last 7 days dates including today.
Ans:
SELECT (sysdate - LEVEL) + 1 AS "Week days"
FROM DUAL
CONNECT BY LEVEL <= 7;
WITH cte(n, days) AS(
SELECT 1, sysdate FROM DUAL
UNION ALL
SELECT n+1, sysdate - n FROM cte
WHERE n+1 <= 7
)
SELECT days AS "Week days" FROM cte;
Correct way of refactoring CONNECT BY queries
Writing queries using recursive WITH
clause or converting a hierarchical query written using Oracle native CONNECT BY
and START WITH
clauses, requires you to understand the hierarchical query execution order in Oracle. Read our Working with Hierarchical query in Oracle database article to master on hierarchical queries in databases.
Let us take an hierarchical query example implemented using CONNECT BY
, START WITH
clauses and refactor to recursive WITH
clause. Example is based on EMP table assuming you have EMP table data as is provided by Oracle database SCOTT schema.
SELECT LEVEL lvl, Empno, Ename, Mgr, Sal
FROM Emp
WHERE Sal > 2500
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
LVL EMPNO ENAME MGR SAL
---------- ---------- ---------- ---------- ----------
1 7839 KING 5000
2 7566 JONES 7839 2975
3 7788 SCOTT 7566 3000
3 7902 FORD 7566 3000
2 7698 BLAKE 7839 2850
WITH ref (lvl, Empno, Ename, Mgr, Sal) AS
(
SELECT 1 AS lvl, Empno, Ename, Mgr, Sal
FROM emp
WHERE Ename = 'KING'
UNION ALL
SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Sal
FROM emp, ref
WHERE ref.empno = emp.mgr
)
SELECT * FROM ref
WHERE Sal > 2500;
LVL EMPNO ENAME MGR SAL
---------- ---------- ---------- ---------- ----------
1 7839 KING 5000
2 7698 BLAKE 7839 2850
2 7566 JONES 7839 2975
3 7788 SCOTT 7566 3000
3 7902 FORD 7566 3000
Refactoring Steps:
- A recursive subquery factoring using
WITH
clause, must contain twoSELECT
query blocks combined by aUNION ALL
set operator. - Put your
START WITH
conditions to the 1stSELECT
query. - Following things must be considered while implementing the 2nd
SELECT
subquery which is the recursive part.- 2nd
SELECT
query is similar to theCONNECT BY
recursion part in a native hierarchical query. It must join theWITH
subquery alias at least once (ref in above case).
- Consider columns from the actual table not from
WITH
subquery alias (ref in above case). - Select columns from
WITH
subquery alias only if the column is chained which means column is incremented/decremented in case of finding level or depth or if column is combined to other columns/values to find a path. - Take column which is followed by
PRIOR
fromWITH
subquery alias and the other column from actual table for implementing join. Considering above example, take Empno fromWITH
subquery alias (which is ref) and Mgr from EMP table. So, your condition"PRIOR Empno = Mgr"
is translated as"ref.empno = emp.mgr"
.
- 2nd
- Your outer query
SELECT
should be based onWITH
subquery alias. CONNECT BY
queryWHERE
clause must be applied to the end in the recursiveWITH
query.

We showed above how the START WITH
, CONNECT BY
constructs are mapped during recursive subquery factoring. But what if the order of steps is not followed and is disturbed.
What happens if filters are applied in the WITH clause subqueries, not to the end?
Let us conclude that with an example and understand the impact when you do not follow the steps in sequence defined.
Scenario 1: Applying filters in subqueries
SELECT LEVEL lvl, Empno, Ename, Mgr, Sal
FROM Emp
WHERE Sal > 2500
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
LVL EMPNO ENAME MGR SAL
---------- ---------- ---------- ---------- ----------
1 7839 KING 5000
2 7566 JONES 7839 2975
3 7788 SCOTT 7566 3000
3 7902 FORD 7566 3000
2 7698 BLAKE 7839 2850
WITH ref (lvl, Empno, Ename, Mgr, Sal) AS
(
SELECT 1 AS lvl, Empno, Ename, Mgr, Sal
FROM emp
WHERE Ename = 'KING'
AND Sal > 2500
UNION ALL
SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Sal
FROM emp, ref
WHERE emp.Sal > 2500
AND ref.empno = emp.mgr
)
SELECT * FROM ref;
LVL EMPNO ENAME MGR SAL
---------- ---------- ---------- ---------- ----------
1 7839 KING 5000
2 7698 BLAKE 7839 2850
2 7566 JONES 7839 2975
3 7788 SCOTT 7566 3000
3 7902 FORD 7566 3000
As you see there is no impact when you moved the filter condition "Sal > 2500"
to both the subqueries. However this may not be true in every case. Let us take one more scenario.
Scenario 2: Shows the impact when filter is not applied to the last
SELECT LEVEL lvl, Empno, Ename, Mgr, Job
FROM Emp
WHERE Job = 'MANAGER'
START WITH Ename = 'KING'
CONNECT BY PRIOR Empno = Mgr;
LVL EMPNO ENAME MGR JOB
---------- ---------- ---------- ---------- ---------
2 7566 JONES 7839 MANAGER
2 7698 BLAKE 7839 MANAGER
2 7782 CLARK 7839 MANAGER
WITH ref (lvl, Empno, Ename, Mgr, Job) AS
(
SELECT 1 AS lvl, Empno, Ename, Mgr, Job
FROM emp
WHERE Ename = 'KING'
AND Job = 'MANAGER'
UNION ALL
SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Job
FROM emp, ref
WHERE emp.Job = 'MANAGER'
AND ref.empno = emp.mgr
)
SELECT * FROM ref;
no rows selected
Isn’t the output strange when you apply filters in the WITH
clause subqueries. This abnormality is because, the 1st subquery returns NULL when filter is applied to it and thus the 2nd subquery which is the recursive part has to join at least once with result set returned from 1st query also returns NULL. Having said that, showing below the correct approach of refactoring.
WITH ref (lvl, Empno, Ename, Mgr, Job) AS
(
SELECT 1 AS lvl, Empno, Ename, Mgr, Job
FROM emp
WHERE Ename = 'KING'
UNION ALL
SELECT ref.lvl+1, emp.Empno, emp.Ename, emp.Mgr, emp.Job
FROM emp, ref
WHERE ref.empno = emp.mgr
)
SELECT * FROM ref
WHERE Job = 'MANAGER';
LVL EMPNO ENAME MGR JOB
---------- ---------- ---------- ---------- ---------
2 7698 BLAKE 7839 MANAGER
2 7782 CLARK 7839 MANAGER
2 7566 JONES 7839 MANAGER
Conclusion
As showed above, you must always keep in mind how CONNECT BY
queries are executed in Oracle database. Through this article we explained how to write and understand hierarchical queries in Oracle databases using CONNECT BY
and then rewriting using recursive subquery factoring. If you still not confident and have some confusions around, do not be agitated as examples are not limited and we bring you tricky questions and answers on hierarchical queries using CONNECT BY and recursive subquery refactoring with more examples.