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 genearte 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 cte where odd_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 .



    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




    Article Tags :
    Practice Tags :


    Be the First to upvote.


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.