LEVEL and WITH Recursive queries in Oracle

This article gives an overview of how to write and understand hierarchical query in Oracle databases using recursive subquery factoring and using LEVEL 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 ALLUNIONINTERSECT or MINUS set operators.

The second query block is known as the recursive member, which must reference the query name at-least once.

Writing queries using recursive WITH clause or converting CONNECT BY hierarchical queries to recursive WITH clause

Here are some examples of using recursive subquery factoring to understand how they work in Oracle database. This code below will help you understand how to write queries using CONNECT BY, START WITH and LEVEL clauses and how you can convert or write the same piece of code using recursive WITH clause.

Q : Generate numbers from 1 to 10.

Ans:

  • Using CONNECT BY
  • Using recursive WITH clause
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:

  • Using CONNECT BY
  • Using recursive WITH clause
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;

You can get more hierarchical queries examples in our below article.

Tricky questions and answers on hierarchical queries using CONNECT BY and recursive subquery refactoring.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments