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.

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.