MySQL | Recursive CTE (Common Table Expressions)

What is a CTE?

In MySQL every query generates a temporary result or relation. In order to give a name to those temporary result set, CTE is used.

• A CTE is defined using WITH clause.
• Using WITH clause we can define more than one CTEs in a single statement.
• A CTE can be referenced in the other CTEs that are part of same WITH clause but those CTEs should be defined earlier.
• The scope of every CTE exist within the statement in which it is defined.

A recursive CTE is a subquery which refer to itself using its own name.

• The recursive CTEs are defined using WITH RECURSIVE clause.
• There should be a terminating condition to recursive CTE.
• The recursive CTEs are used for series generation and traversal of hierarchical or tree-structured data.

Syntax:

WITH RECURSIVE 
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;


cte_name: Name given to recursive subquery written in subquery block.
col1, col2, ...colN: The name given to columns generated by subquery.
subquery: A MySql query that refer to itself using cte_name as its own name.

Here, the column names given in SELECT statement should match with the names provided in list followed by cte_name.

The recursive CTE structure provided in the subquery block:

Select col1, col2, ... coln from table_name        
UNION [ALL, DISTINCT]
Select col1, col2, ...coln from cte_name          
WHERE clause

The recursive CTE consist of a non-recursive subquery followed by a recursive subquery-

• The first select statement is a non-recursive statement, which provides initial rows for result set.
• UNION [ALL, DISTINCT] is use to add additional rows to previous result set. Use of ALL and DISTINCT keyword are used to include or eliminate duplicate rows in the last result set.
• The second select statement is a recursive statement which produces result set iteratively until the condition provided in WHERE clause is true.
• The result set produced at each iteration take result set produced at previous iteration as the base table.
• The recursion ends when the recursive select statement doesn’t produce any additional rows.

Examples:

1. Consider, following query which generate a series of first 5 odd numbers –
   Query:

   WITH RECURSIVE 
   odd_no (sr_no, n) AS
   (
   SELECT 1, 1 
   union all
   SELECT sr_no+1, n+2 from odd_no where sr_no < 5 
   )
   SELECT * FROM odd_no;  
   

   Output:

   +---------+-------+
   | sr_no   |  n    |
   +---------+-------+
   | 1       |  1    |
   | 2       |  3    |
   | 3       |  5    |
   | 4       |  7    |
   | 5       |  9    |
   +---------+-------+
   

   Explanation:

   The above query consists of two parts- non-recursive and recursive.

   Non-Recursive part- It will produce initial rows which consist of two columns named as “sr_no” and “n” and single row.

   Query:
   SELECT 1, 1 
   
   Output:
   +---------+-------+
   | sr_no   |  n    |
   +---------+-------+
   | 1       |  1    |
   +---------+-------+
   

   Recursive part-

   SELECT sr_no+1, n+2 from cte where odd_no < 5 
   

   It will add rows to previous output till the terminating condition i.e ( sr_no < 5 ) is satisfied.

   When, the sr_no become 5 the condition become false and the recursion is terminated.

2. Consider the following “bst” table-

   mysql> SELECT * FROM bst order by node;
   +------+-----------+
   | node | parent    | 
   +------+-----------+
   |  1   |  NULL     |
   |  2   |   1       |
   |  3   |   1       |
   |  4   |   2       |
   |  5   |   2       | 
   |  6   |   3       |
   |  7   |   3       |
   +------+-----------+
   

   The above table “bst” consist of two columns “node” and “parent” which gives the value of a node in the binary search tree and their respective parent value.

   Problem description: We have to find paths of all the node in the given “bst”.

   Query:

   WITH RECURSIVE
   cte ( node, path )
   AS
   ( SELECT node, cast ( 1 as char(30) )  
             FROM bst WHERE parent IS NULL
     UNION ALL
     SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ) 
            FROM cte JOIN bst ON cte.node = bst.parent
   )
   SELECT * FROM cte ORDER BY node;
   

   Output:

   +------+-----------+
   | node |   path    |
   +------+-----------+
   |  1   | 1         |
   |  2   | 1-->2     |
   |  3   | 1-->3     |
   |  4   | 1-->2-->4 |
   |  5   | 1-->2-->5 |
   |  6   | 1-->3-->6 |
   |  7   | 1-->3-->7 |
   +------+-----------+
   

   Explanation:

   Here, non-recursive part in above CTE will give only a single row that consists of a root node along with its path which is set as 1.

   SELECT node, cast ( 1 as char(30) )  
             FROM bst WHERE parent IS NULL
   
   Output:
   +------+-----------+
   | node |   path    |
   +------+-----------+
   |  1   | 1         |
   +------+-----------+
   

   Recursive part-

   SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ) 
            FROM cte JOIN bst ON cte.node = bst.parent
   

   The recursive SELECT statement will find all those nodes in bst whose parent is the node produced in the previous iteration.

   Such iteration ends when node i.e(leaf node) produced in the previous iteration doesn’t consist of any child node in bst .