How to Implement Recursive CTE for Hierarchical Query to MariaDB?

Hierarchical data structures such as organizational hierarchies, file systems or product sections are common to find in the database. MariaDB an open-source relational database management system, offers several methods for querying hierarchical information, among them Recursive Common Table Expressions (CTEs) as a highly efficient and versatile option.

In this article, we will learn various methods of using Recursive CTEs in hierarchical queries of MariaDB to serve to different situations.

Understanding Recursive CTEs

• Recursive CTEs carry out repetitive queries on hierarchical data by doing SELECT statement which contains CTE itself an infinite number of times.
• This functionality is primarily useful when dealing with and querying data which is presented in a hierarchical structure to optimize the output results.

Employees Table:

To understand How to Implement Recursive CTE for Hierarchical Query to MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called employees which contains employee_id, name, and manager_id  as Columns.

+-------------+---------------+------------+
| employee_id | name          | manager_id |
+-------------+---------------+------------+
| 1           | John Doe      | NULL       |
| 2           | Jane Smith    | 1          |
| 3           | Alice Johnson | 2          |
+-------------+---------------+------------+

Method 1: Direct Recursive CTE

This method directly defines a Recursive Common Table Expression (CTE) within the query. It recursively traverses the hierarchical data structure in a single query, making it concise and efficient.

Steps to follows:

• Define the Recursive CTE with an anchor member and a recursive member.
• Use the CTE in subsequent SQL statements to retrieve hierarchical data.

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Output:

+-------------+-------------+------------+
| employee_id | name        | manager_id |
+-------------+-------------+------------+
| 1           | John Doe    | NULL       |
| 2           | Jane Smith  | 1          |
| 3           | Alice Johnson | 2         |
+-------------+-------------+------------+

Explanation:

• The hierarchical structure of employees in the organization is shown on the output. Each line stands for a employee with his employee_id, name, and manager_id among the data columns.
• The manager_id attribute points to an employee’s manager, where NULL values shows top-level employees who have no manager.

Method 2: Use Temporary Table with Recursive CTE

It is an approach where a table is formed usually on a temporary basis to contain the hierarchical data on it. It offers more freedom in complex querying scenarios and performance for repeated queries can be increased.

Steps to follows:

• Define the Recursive CTE within a CREATE TEMPORARY TABLE statement.
• Query the temporary table to retrieve hierarchical data.
• Optionally drop the temporary table when it is no longer needed.

Example:

CREATE TEMPORARY TABLE TempHierarchy AS (
    WITH RECURSIVE Hierarchy AS (
        SELECT employee_id, name, manager_id
        FROM employees
        WHERE manager_id IS NULL
        
        UNION ALL
        
        SELECT e.employee_id, e.name, e.manager_id
        FROM employees e
        JOIN Hierarchy h ON e.manager_id = h.employee_id
    )
    SELECT * FROM Hierarchy
);

SELECT * FROM TempHierarchy;
DROP TEMPORARY TABLE IF EXISTS TempHierarchy;

Output:

+-------------+-------------+------------+
| employee_id | name        | manager_id |
+-------------+-------------+------------+
| 1           | John Doe    | NULL       |
| 2           | Jane Smith  | 1          |
| 3           | Alice Johnson | 2         |
+-------------+-------------+------------+

Explanation: This method involves creating a temporary table named TempHierarchy to store the hierarchical data temporarily. The output of and the temporary table is the same as Method 1. After querying, the temporary table is dropped to release resources

Conclusion

Overall, MariaDB provides flexible techniques of creating Recursive “Common Table Expressions” to navigate the hierarchical data precisely. Direct usage of CTEs, writing views or temporary tables and any of these methods has its own benefits as it is based on the exact requirements of your application.