Recursive Join in SQL

A recursive query is a powerful feature that allows us to query hierarchical data which are used in relational databases. They are a compound operation that helps in accumulating records every time until a repetition makes no change to the result. 

Recursive queries are useful to build a hierarchy tree, best in working with hierarchical data such as org charts for the bill of materials traverse graphs or to generate arbitrary row sets. This involves joining a set with itself an arbitrary number of times. A recursive query is usually defined by the anchor part and the recursive part.

Recursive joins are sometimes also called “fixed-point joins”. They are used to obtain the parent-child data. In SQL Recursive joins are implemented with recursive common table expressions. Recursive common table expression (CTEs) is a  way to reference a query over and over again. 

Now we understand the Recursive Join in SQL by using an example.

Step 1: First we create a database of employees, Where  Common Table Expression of the company for its Employee Id, Employee name, Employee age.

Query:

CREATE TABLE employees (
 id serial,
 name varchar(20),
  age int
);

Step 2: In this step insert values into an employee table.

Query:

INSERT INTO employees VALUES (1, 'Ankit', 32);
INSERT INTO employees VALUES (2, 'Ayush', 31);
INSERT INTO employees VALUES (3, 'Piyush', 42);
INSERT INTO employees VALUES (4, 'Ramesh', 31);
INSERT INTO employees VALUES (5, 'Rohan', 29);
INSERT INTO employees VALUES (6, 'Harry', 28);
INSERT INTO employees VALUES (7, 'Rohit', 32);
INSERT INTO employees VALUES (8, 'Gogi', 32);
INSERT INTO employees VALUES (9, 'Tapu', 33);
INSERT INTO employees VALUES (10, 'Sonu', 40);

Step 3: A statement that gives all the reports that roll up into a certain organization within the company. A CTE is defined using a WITH statement, followed by a table expression definition. The AS command is used to rename a column or table with an alias. A recursive CTE must contain a UNION  statement and be recursive.

Query:

WITH RECURSIVE managertree AS (
 SELECT id, name, age
 FROM employees
 WHERE id = 1 
 UNION ALL
 SELECT e.id, e.name, e.age
 FROM employees e
 INNER JOIN managertree mtree ON mtree.id = e.age
)

Step 4: To check the recursive join data we use the following query.

Query:

SELECT * FROM managertree;

Output: