Oracle sQL RECURSIVE Query

Oracle provides the START WITH and CONNECT BY clauses to extract data recursively from a table containing hierarchical data.

Syntax of START WITH ... CONNECT BY:
SELECT <expression>
FROM <TABLE>
WHERE <condition>
START WITH <initial_condition>
CONNECT BY [NOCYCLE] PRIOR <recursion_condition>;

START WITH is used specify the root row in the hierarchy.

CONNECT BY is used to specify the relationship between the parent row and the child row. 

PRIOR can be used on either side of the operator. It should be used with the parent row in the condition.


Example

Consider the table below, it has a hierarchical relationship, each employee has a manager who is upper in the hierarchy. We can execute a SQL recursive query on this table to extract the hierarchical data.

SQL Recursive Query Example
SELECT NAME, ROLE
FROM EMP_HIERARCHY
START WITH EMPID=3
CONNECT BY PRIOR EMPID=MGR;

The above SQL statement would produce the following output:

NAME  ROLE
ROB VP
STEVE MANAGER
TIM MANAGER
TONY MANAGER

LEVEL Pseudocolumn

We can make use of certain pseudocolumns to extract some useful information while using START WITH ... CONNECT BY. Level Pseudocolumn is used to return the depth of the row in the hierarchy.

CONNECT_BY_ROOT Pseudocolumn

It is used to get the root of the hierarchy to which the current row belongs to.

CONNECT_BY_ISLEAF Pseudocolumn

It is used to identify if the current row is the leaf node in the hierarchy.

SYS_CONNECT_BY_PATH

It is used to get the complete hierarchy path from the root to the current row.

Example:

Considering the table used in our previous example, lets see an how to use the above mentioned pseudocolumns:

SELECT NAME, ROLE, LEVEL,
CONNECT_BY_ROOT NAME AS VP,
SYS_CONNECT_BY_PATH(NAME, '-->') AS HIERARCHY,
CONNECT_BY_ISLEAF
AS ISLEAF FROM EMP_HIERARCHY START WITH EMPID IN (3, 4) CONNECT BY PRIOR EMPID = MGR;

Shown below is the output of the above query.

NAME ROLE LEVEL VP HIERARCHY ISLEAF
ROB VP 1 ROB -->ROB 0
STEVE MANAGER 2 ROB -->ROB-->STEVE 1
TIM MANAGER 2 ROB -->ROB-->TIM 1
TONY MANAGER 2 ROB -->ROB-->TONY 1
JACK VP 1 JACK -->JACK 0
MARK MANAGER 2 JACK -->JACK-->MARK 1
GLEN MANAGER 2 JACK -->JACK-->GLEN 1
TOM MANAGER 2 JACK -->JACK-->TOM 1

We can notice from the output that the LEVEL column gives the depth of each row in its hierarchy.

The VP column gives the root of the hierarchy. We used the name column in the query (CONNECT_BY_ROOT NAME AS VP), hence it outputs the name column of the root row.

The HIERARCHY column gives the path of the each row form root to current row.

The ISLEAF column identifies if the row is a leaf node in the hierarchy.

NOCYCLE

The NOCYCLE parameter is handy when a loop exists in the hierarchy. These kind of relationships can cause the SQL query to break because of the loops. The NOCYCLE paramater can be used with these query to avoid the error.

CONNECT_BY_ISCYCLE Pseudocolumn

We can make use of the CONNECT_BY_ISCYCLE pseudocolumn to identify the row where the loop exists. Note that it can only be used with the NOCYCLE parameter.

Example:

Consider the below table having a cyclic relationship.

SQL Recursive Loop Query Example

Using CONNECT BY query on the above table would throw as error saying "CONNECT BY loop in user data". In order to run the query successfully we need to use the NOCYCLE parameter as shown below:

SELECT NAME, ROLE, LEVEL, CONNECT_BY_ISCYCLE AS ISLOOP,
CONNECT_BY_ROOT NAME AS VP,
SYS_CONNECT_BY_PATH(NAME, '-->') AS HIERARCHY  
FROM EMP_HIERARCHY
START WITH EMPID IN (3, 4)
CONNECT BY NOCYCLE PRIOR EMPID=MGR;

Below is the output generated by the query:

NAME ROLE LEVEL ISLOOP VP HIERARCHY
ROB VP 1 0 ROB -->ROB
TONY MANAGER 2 0 ROB -->ROB-->TONY
MARK MANAGER 2 0 ROB -->ROB-->MARK
TOM MANAGER 2 0 ROB -->ROB-->TOM
JACK VP 1 0 JACK -->JACK
TIM MANAGER 2 0 JACK -->JACK-->TIM
GLEN MANAGER 3 0 JACK -->JACK-->TIM-->GLEN
STEVE MANAGER 4 1 JACK -->JACK-->TIM-->GLEN-->STEVE

Notice the use of CONNECT_BY_ISCYCLE pseudocolumn to identify the row where loop exists in the hierarchy.

POPULAR ARTICLES

Creating Conditional Beans in Spring

The concept of condition beans enables Spring to restrict the creation of any bean depending on the evaluation of a condition. These beans get created only when a preset condition is evaluated as true

View Article

Accepting Request Param and Path Variable in Spring Controller

Spring MVC provides various ways through which a client browser can pass data to the Controller. In this article we will discuss about accepting Request Parameters and Path Variables in Spring Contr..

View Article

Generate Namespace & Schema Information using JAXB

Most xml documents used in enterprise applications makes use of namespace to avoid element name conflicts. This article talks about generating these namespace and schema information when marshaling...

View Article

Switching Database Profile using Spring Profiles

We are most likely to have separate db configuration for different environment like development and production environment. Spring profiles provide a convenient way to switch db profiles at runtime.

View Article

SQL and its Sub-Languages

SQL (Structured Query Language) is a language understood by most modern databases. It is an ANSI (American National Standard Institute) standard language which is used to manipulate databases.

View Article

Introducing JUnit Rule

Junit Rules allows developers to add additional functionalities that can applied to all test methods in a test class. It is similar to the concept of custom test runners but with reduced restrictions.

View Article

Addressing Ambiguity in Spring Autowiring

Spring autowiring is powerful concept, but we should be very cautious while using it. We may end up in creating ambiguity while autowiring beans, which will cause autowiring to fail.

View Article

Creating and Using Synonym in Oracle Database

Synonyms are database objects used to provide duplicate names to existing objects in the database. It is just an alternate name used to hide the original name of the object.

View Article

Creating and Using Sequence in Oracle Database

A sequence is used to auto-generate numbers in ascending or descending order which can serve as a primary key or a part of it (in case of composite key).

View Article

Creating and Manipulating Constraints in Oracle Database

Constraints are used to impose certain rules on columns to avoid invalid data entry into the table. If any of the constraint is violated the operation fails.

View Article

Integrating Log4J with Perf4J for Performance Logging

Perf4j is an open source logging framework used primarily for monitoring performance statistics in java applications. Log4j has the ability to integrate with perf4j to capture performance data.

View Article

Tagging in GIT

Tagging allows us to mark a specific point in the commit history or snapshot. A tag is typically used to mark a project release. This article shows how to create tags in Git.

View Article