How to Create a MySQL Hierarchical Recursive Query

Mysql Sql Hierarchical Data Recursive Query

In MySQL, there are various ways to create recursive queries to retrieve hierarchical data. In this article, we will explore different methods to achieve this and provide examples.

Table Structure

First, let's consider the table structure that we will be using in our examples. We have a table with two columns, 'id' and 'parent_id', which represents a hierarchical relationship between the rows.


CREATE TABLE hierarchy (
    id INT PRIMARY KEY,
    parent_id INT
);

Here is a sample data for the table:


INSERT INTO hierarchy (id, parent_id) VALUES
    (1, NULL),
    (2, 1),
    (3, 1),
    (4, 2),
    (5, 4),
    (6, 4),
    (7, 3),
    (8, 5),
    (9, 6),
    (10, 7);

Method 1: MySQL Recursive CTE (Common Table Expressions)

Starting from MySQL 8.0, you can use Common Table Expressions (CTE) to create recursive queries. CTE allows you to define temporary named result sets that you can reference within a query.

To create a recursive query using CTE, you need to define two queries:

  • The anchor member: This is the initial query that selects the rows at the top of the hierarchy.
  • The recursive member: This is the query that refers to the CTE and continues iterating over the hierarchy.

WITH RECURSIVE cte AS (
    SELECT id, parent_id
    FROM hierarchy
    WHERE id = 19 -- Replace with your desired id
    UNION ALL
    SELECT h.id, h.parent_id
    FROM hierarchy h
    INNER JOIN cte ON h.parent_id = cte.id
)
SELECT id
FROM cte;

In this example, we start the recursive query with the row where the id is 19. The recursive member then joins the hierarchy table with the cte table using the parent_id column to continue iterating over the hierarchy.

The result of this query is:

+----+
| id |
+----+
| 20 |
| 21 |
| 22 |
+----+

With CTE, you can easily retrieve the children of a specific id in a hierarchical structure.

Method 2: Nested Set Model

Another popular method for representing hierarchical data is by using the Nested Set Model. This model assigns two columns, 'lft' and 'rgt', to each row, which define the boundaries of the subtree.

With this model, you can retrieve the children of a specific id by selecting the rows that have a left value greater than the id's left value and a right value less than the id's right value.


SELECT id
FROM hierarchy
WHERE lft > (
    SELECT lft
    FROM hierarchy
    WHERE id = 19 -- Replace with your desired id
) AND rgt < (
    SELECT rgt
    FROM hierarchy
    WHERE id = 19 -- Replace with your desired id
);

The result of this query is the same as the previous example:

+----+
| id |
+----+
| 20 |
| 21 |
| 22 |
+----+

The Nested Set Model provides a different way to represent hierarchical data and allows for efficient queries to retrieve the children.

Method 3: Adjacency List Model

The Adjacency List Model is the simplest way to represent hierarchical data. It uses a single 'parent_id' column to establish the parent-child relationship. To retrieve the children of a specific id, you can simply query the rows with the matching parent_id.


SELECT id
FROM hierarchy
WHERE parent_id = 19 -- Replace with your desired id

The result of this query is:

+----+
| id |
+----+
| 20 |
| 21 |
| 22 |
+----+

The Adjacency List Model is the most straightforward method, but it can be less efficient for large hierarchies since it requires multiple queries to retrieve deeper levels of the hierarchy.

Conclusion

In this article, we have explored different methods to create a MySQL hierarchical recursive query. We have discussed the MySQL Recursive CTE, the Nested Set Model, and the Adjacency List Model. Each method has its own advantages and may be more suitable depending on your specific use case.

Related Questions