Recursive queries are essential for hierarchical data formats such as charts, trees, and file directories, enabling effective navigation of parent-child relationships in MySQL. Hierarchical queries assist in fetching data encapsulating various levels of associations. In this article, you will investigate the various techniques for constructing a hierarchical recursive query in MySQL thoroughly, accompanied by examples for each method.

Table of Contents:

• What is Hierarchical Recursive Query in MySQL?
• Why is it necessary to create a Hierarchical Recursive Query in MySQL?
• Benefits of developing a Hierarchical Recursive Query in MySQL
• Techniques to Formulate a Hierarchical Recursive Query in MySQL
  • Method 1: Utilizing Recursive Common Table Expression in MySQL
  • Method 2: Implementing the Adjacency List Model in MySQL
  • Method 3: Applying the Nested Set Model in MySQL
  • Method 4: Employing the Materialized Path Model in MySQL
  • Method 5: Utilizing the Closure Table Model in MySQL
  • Method 6: Utilizing the Path Enumeration Model in MySQL
• Alternative Approaches to Formulating a Hierarchical Recursive Query in MySQL JSON or NoSQL
  • Employing Graph-Based Approach in MySQL JSON or NoSQL
• Performance Evaluation of Each Technique
• Practical Examples
• Optimal Practices
• Summary

What is Hierarchical Recursive Query in MySQL?

Hierarchical Recursive Query in MySQL enables the querying of data organized in a tree-like parent-child framework. MySQL accommodates such queries via the WITH RECURSIVE Common Table Expression (CTE). It initiates with a base case (anchor member) and recursively enacts the recursive step. The recursion concludes when no further child records are available to retrieve.

Why is it necessary to create a Hierarchical Recursive Query in MySQL?

The capability of Hierarchical Recursive Queries in MySQL is crucial for managing parent-child dynamics like organizational charts and file systems, ensuring efficient navigation through multi-level associations, which is often challenging with standard SQL joins. Examples include retrieving nested categories in e-commerce, exploring folder hierarchies, and listing employees under a manager.

Benefits of developing a Hierarchical Recursive Query in MySQL

• Simplifies Complex Queries: Enhanced clarity and optimization occur in these intricate queries by eliminating the necessity for numerous self-joins.
• Less Redundancy: These queries are more streamlined and simpler to uphold as they do not require manual recursive lookups from application logic.
• Increased Performance: Sequential querying is rendered unnecessary since this hierarchical data structure can be extracted in a singular query instead of multiple separate queries.

Before delving into the methods, let’s establish a table called Folders and populate it with some data to serve as an example for the upcoming techniques.

CREATE TABLE folders (
    id INT PRIMARY KEY,
    folder_name VARCHAR(100),
    parent_id INT NULL,          
    path VARCHAR(255) UNIQUE,   
    lft INT NOT NULL,           
    rgt INT NOT NULL            
);

-- Inserting values into the table
INSERT INTO folders (id, folder_name, parent_id, path, lft, rgt) VALUES
(1, 'Root', NULL, '/', 1, 14),
(2, 'Documents', 1, '/Documents', 2, 7),
(3, 'Images', 2, '/Documents/Images', 3, 4),
(4, 'Videos', 2, '/Documents/Videos', 5, 6),
(5, 'Downloads', 1, '/Downloads', 8, 13),
(6, 'Software', 5, '/Downloads/Software', 9, 10),
(7, 'Music', 5, '/Downloads/Music', 11, 12);

-- To view the table  
SELECT * FROM folders;

Here is how the table appears after it is created and populated with values.

Techniques to Formulate a Hierarchical Recursive Query in MySQL

There are various techniques available for generating a Hierarchical Query in MySQL, each suited for different scenarios. One of the most effective methods is to utilize a Recursive Common Table Expression to construct a Hierarchical Query.

Method 1: Utilizing Recursive Common Table Expression in MySQL

A Recursive CTE is employed to retrieve hierarchical data by continuously joining the table with itself until the complete hierarchy is collected.

Syntax:

WITH RECURSIVE cte_name AS (
    -- Base Case (Starting Point)
    SELECT column1, column2, ..., 1 AS level
    FROM table_name
    WHERE condition_for_root_nodes
    UNION ALL
    -- Recursive Case (Joins Table with Itself)
    SELECT t.column1, t.column2, ..., cte.level + 1
    FROM table_name t
    INNER JOIN cte_name cte ON t.parent_column = cte.id
)
SELECT * FROM cte_name;

Example:

WITH RECURSIVE folder_hierarchy AS (
    -- Base Case: Start from the Root folder
    SELECT id, folder_name, parent_id, path, 1 AS level
    FROM folders
    WHERE parent_id IS NULL
    UNIONALL
    -- Recursive Scenario: Retrieve child directories
    SELECT f.id, f.folder_name, f.parent_id, f.path, fh.level + 1
    FROM folders f
    INNER JOIN folder_hierarchy fh ON f.parent_id = fh.id
)
SELECT * FROM folder_hierarchy;

Result:

Clarification: Here, the SELECT statement retrieves the (parent_id IS NULL) as a foundational class and labels it as level = 1. Subsequently, the recursive segment combines the Folders table with the CTE itself.

Method 2: Utilizing the Adjacency List Model in MySQL

This Adjacency List Model illustrates the hierarchical information through a self-referential foreign key, where each entry holds a reference (parent_id) to its immediate ancestor.

Format:

SELECT parent.* FROM table_name AS child
JOIN table_name AS parent ON child.parent_id = parent.id
WHERE child.id = ?;

Sample:

-- Query to find the Parent of the specified folder 
SELECT parent.*
FROM folders AS child
JOIN folders AS parent ON child.parent_id = parent.id
WHERE child.id = 3; 

Result:

Clarification: Here, the JOIN statement merges the table with itself using parent_id, obtaining the parent folder of a specified child by aligning child.parent_id = parent.id. The WHERE clause narrows down to a particular child (id=3), yielding its parent details

Method 3: Applying the Nested Set Model in MySQL

The Nested Set Model portrays hierarchical information using left and right values, enhancing the efficiency of the retrieval process.

Format:

SELECT child.column_name, (COUNT(parent.column_name) - 1) AS depth
FROM table_name AS child
JOIN table_name AS parent 
    ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.column_name = 'randomvalue'
GROUP BY child.id
ORDER BY child.lft;

Sample:

SELECT child.folder_name, child.lft, child.rgt, 
       (COUNT(parent.id) - 1) AS depth
FROM folders AS child
JOIN folders AS parent 
    ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.folder_name = 'Documents'
GROUP BY child.id
ORDER BY child.lft;

Result:

Clarification: To determine hierarchical relationships, the JOIN statement merges the table with itself using lft and rgt. The COUNT() function computes the depth and filters the output based on the specified parent node.

Method 4: Utilizing the Materialized Path Model in MySQL

The Materialized Path exemplifies hierarchical information by recording the complete path of a node as a delimited string. 

Format:

SELECT column_name1, column_name2, 
       LENGTH(path_column) - LENGTH(REPLACE(path_column, 'delimiter', '')) AS depth
FROM table_name
ORDER BY path_column;

Sample:

SELECT folder_name, path, 
       LENGTH(path) - LENGTH(REPLACE(path, '/', '')) AS depth
FROM folders
WHERE path LIKE '/Documents/%'
ORDER BY path;

Result:

Clarification: The WHERE clause filters entries whose path begins with /Documents/, ensuring that only descendants of ‘Documents’ are selected.

Method 5: Employing the Closure Table Model in MySQL

The Closure Table Model delineates hierarchical associations in a distinct table by sustaining all ancestor-descendant pairs.

Format:

CREATE TABLE table_name (
    id INT PRIMARY KEY,      
    name VARCHAR(100)         
);

CREATE TABLE table_closure (
    ancestor INT,             
    descendant INT,           
    depth INT,                
    FOREIGN KEY (ancestor) REFERENCES table_name(id),
    FOREIGN KEY (descendant) REFERENCES table_name(id)
);

Sample:

Let us consider the following table as an illustration for improved clarity.

CREATE TABLE folders (
    id INT PRIMARY KEY,
    folder_name VARCHAR(100)
);

CREATE TABLE folder_closure (
    ancestor INT,
    descendant INT,
    depth INT,
    PRIMARY KEY (ancestor, descendant),
    FOREIGN KEY (ancestor) REFERENCES folders(id),
    FOREIGN KEY (descendant) REFERENCES folders(id)
);

-- Insert folders
INSERT INTO folders (id, folder_name) VALUES
(1, 'Root'),
(2, 'Documents'),
(3, 'Images'),
(4, 'Videos'),
(5, 'Downloads'),
(6, 'Software'),
(7, 'Music');

-- Insert ancestor-descendant relationships
INSERT INTO folder_closure (ancestor, descendant, depth) VALUES
(1, 1, 0), (2, 2, 0), (3, 3, 0), (4, 4, 0), (5, 5, 0), (6, 6, 0), (7, 7, 0),  
(1, 2, 1), (1, 3, 2), (1, 4, 2), (1, 5, 1), (1, 6, 2), (1, 7, 2),  
(2, 3, 1), (2, 4, 1),  
(5, 6, 1), (5, 7, 1);  

-- Query to get all the descendants of documents (id=2)
SELECT f.folder_name, fc.depth
FROM folder_closure fc
JOIN folders f ON fc.descendant = f.id
WHERE fc.ancestor = 2
ORDER BY fc.depth;

Result:

Clarification: In this instance, the JOIN statement merges the folder_closure table with folders using descendant = id, which yields all descendants of Documents

Method 6: Utilizing the Path Enumeration Model in MySQL

This Path Enumeration Model conveys hierarchical data by preserving the complete path of every node as a string.

Format:

CREATE TABLE table_name (
    id INT PRIMARY KEY,       
    name VARCHAR(100),        
);

Sample:

Let us look at the following table as a reference for enhanced understanding.

-- Create table structure using path enumeration
CREATE TABLE categories (
    id INT PRIMARY KEY,
    category_name VARCHAR(100),
    path VARCHAR(255) UNIQUE
);

-- Insert some values into it
INSERT INTO categories (id, category_name, path) VALUES
(1, 'Electronics','/1'),  
(2, 'Mobile Devices', '/1/2'),
(3, 'Notebooks', '/1/3'),
(4, 'Add-ons', '/1/2/4'),
(5, 'Power Supplies', '/1/2/4/5'),
(6, 'Video Games', '/1/3/6');

-- Request to retrieve all subdivisions of ‘Mobile Devices’ (id=2)
SELECT * FROM categories WHERE path LIKE '/1/2/%';

Output:

Clarification: The SELECT request successfully obtains all mobile device subcategories by pinpointing all categories whose paths initiate with /1/2/.

Alternative Approaches to Develop a Hierarchical Recursive Query in MySQL JSON or NOSQL

Other strategies, such as the Graph-Based Technique and document-oriented architectures, can similarly be employed to formulate a Hierarchical Recursive Query in MySQL JSON or NOSQL.

Implementing Graph-Based Technique in MySQL JSON or NOSQL

Within the graph-based technique, the hierarchical associations are recorded in a JSON field of a MySQL table.

Format:

CREATE TABLE table_name (
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    hierarchy JSON  
);

Instance:

CREATE TABLE table_name (
    id INT PRIMARY KEY,  
    name VARCHAR(100),  
    hierarchy JSON  
);

INSERT INTO table_name (id, name, hierarchy) VALUES
(1, 'Root', JSON_ARRAY()),  
(2, 'Category A', JSON_ARRAY(1)),  
(3, 'Subcategory A1', JSON_ARRAY(2)),  
(4, 'Category B', JSON_ARRAY(1)),  
(5, 'Subcategory B1', JSON_ARRAY(4));

-- Request to locate direct offspring of a Parent
SELECT name, hierarchy  
FROM table_name  
WHERE JSON_CONTAINS(hierarchy, '1');  

Output:

Clarification: A node with an ancestor of 1 can be efficiently filtered using the JSON_CONTAINS() function.

Comparison of Performance Among Various Techniques

Technique	Application	Advantages	Disadvantages
Recursive CTE	Applicable when dynamic hierarchical queries are essential	Straightforward to implement	Unavailable in earlier versions of MySQL
Adjacency List Model	Useful for managing simple parent-child relationships	Small hierarchies can be easily organized and executed	Poor performance on deep hierarchies due to multiple self-joins
Nested Set Model	Ideal when retrieving the full subtree is frequent	Descendants can be fetched with a single request	Complexity in update and insert actions
Materialized Path Model	Suitable when hierarchical paths need to be kept as strings for navigation	Simplified navigation without using joins	String manipulation is needed while modifying paths
Closure Table Model	Utilized in applications needing to find both ancestors and descendants regularly	Facilitates intricate Queries	Requires additional space as it maintains all possible pairs in the table
Path Enumeration Model	Effectively accommodates file system architectures and other hierarchical searches by maintaining indexed paths	Strengthening the index supports extensive hierarchies	String operations can be costly

Practical Applications

1. Company Structure 

Every worker has a supervisor in a database that contains the personnel hierarchy of the organization. For a specific employee, we need to retrieve the entire hierarchy.

Instance:

-- Establish an Employee table
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT NULL
);

-- Insert some values into it
INSERT INTO employee (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

-- Request to identify the Employee hierarchy
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employee
    WHERE id = 1  
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employee e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

Output:

Clarification: By designating a hierarchy level and determining which employees report to each manager, the recursive query in this case constructs the organizational hierarchy dynamically.

2. Product Categories

E-commerce sites aim to efficiently gather all subcategories when product information is kept in a hierarchical format.

Instance:

-- To establish a category table
CREATE TABLE categories (
    id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL
);

-- To insert some values
INSERT INTO categories (id, category_name, lft, rgt) VALUES
(1, 'Electronics', 1, 10),
(2, 'Notebooks', 2, 5),
(3, 'Gaming Notebooks', 3, 4),
(4, 'Mobile Phones', 6, 9),
(5, 'Smartphones', 7, 8);

-- Request to find all subcategories of Electronics
SELECT c2.category_name
FROM categories c1
JOIN categories c2 ON c2.lft BETWEEN c1.lft AND c1.rgt
WHERE c1.category_name = 'Electronics';

Output:

Clarification:  By selecting categories whose lft and rgt values lie within the boundaries of “Electronics,” this query retrieves all subcategories of that category.

Recommended Practices

• Select a Suitable Model based on efficiency, complexity, and capability.
• Establish indices on the hierarchy columns to enhance query performance, especially for deep hierarchies.
• Uphold Data Integrity.on structured connections, which is accomplished via the implementation of foreign keys.

Final Thoughts

Selecting the right model is crucial for efficient management of hierarchical data in MySQL. Each technique, including Recursive CTE, Adjacency List, Nested Set, Materialized Path, Closure Table, and Path Enumeration, comes with its own benefits and use cases. You are now more informed about the different methodologies for crafting hierarchical recursive queries in MySQL, along with the optimal strategies for implementing them.

If you wish to deepen your knowledge of SQL functionalities, consider enrolling in this SQL course and also visit SQL Interview Questions curated by professionals in the field.

Hierarchical Recursive Query in MySQL – Frequently Asked Questions

The article Hierarchical Recursive Query in MySQL was first published on Intellipaat Blog.