Exploring MySQL Hierarchical Recursive Queries

Prerequisites:

To follow along with the code examples in this article, you'll need the following:

1. MySQL installed on your system.
2. Basic familiarity with SQL and MySQL.

Understanding Recursive Queries:

Recursive queries, also known as recursive common table expressions (CTEs), allow us to traverse hierarchical data structures by repeatedly querying and combining results until a specific condition is met. In MySQL, recursive queries involve the use of CTEs to build and expand upon result sets iteratively.

Creating the Database Schema:

Before we delve into the code examples, let's first set up a sample database schema to work with. For the purpose of this article, let's consider a hypothetical scenario where we have an employee table representing an organizational hierarchy. Each row in the table contains information about an employee and their direct supervisor.

CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
supervisor_id INT,
FOREIGN KEY (supervisor_id) REFERENCES employee(id)
);
INSERT INTO employee (id, name, supervisor_id) VALUES
(1, 'John', NULL),
(2, 'Jane', 1),
(3, 'Mike', 2),
(4, 'Sarah', 1),
(5, 'Emily', 4);

The above SQL code creates an employee table with an "id" column, a "name" column, and a "supervisor_id" column, which references the "id" column itself to establish the hierarchical relationship.

Example: Retrieve the Full Hierarchy

One common use case is to retrieve the entire hierarchy of an organization or a specific branch. Let's start by retrieving the complete hierarchy for all employees in the table.

WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, supervisor_id, 0 AS depth
FROM employee
WHERE supervisor_id IS NULL
UNION ALL
SELECT e.id, e.name, e.supervisor_id, eh.depth + 1
FROM employee e
JOIN employee_hierarchy eh ON e.supervisor_id = eh.id
)
SELECT id, name, depth
FROM employee_hierarchy
ORDER BY depth, id;

In the above query, we define a recursive CTE called "employee_hierarchy." The initial anchor member retrieves the top-level employees (those with a NULL supervisor_id). The recursive member then joins the "employee" table with the CTE to expand the hierarchy by one level at a time until there are no more matching rows. The "depth" column is used to keep track of the depth level.

Example: Retrieve Subordinate Employees

Now, let's suppose we want to retrieve all the subordinates (direct and indirect reports) of a specific employee. We can modify the previous query to achieve this:

WITH RECURSIVE subordinate_employees AS (
SELECT id, name, supervisor_id, 0 AS depth
FROM employee
WHERE id = 2 -- Change the ID to the desired employee
UNION ALL
SELECT e.id, e.name, e.supervisor_id, se.depth + 1
FROM employee e
JOIN subordinate_employees se ON e.supervisor_id = se.id
)
SELECT id, name, depth
FROM subordinate_employees
ORDER BY depth, id;

In the above query, we specify the desired employee by changing the ID in the anchor member. The recursive member then continues to expand the hierarchy by joining with the CTE until all subordinate employees have been retrieved.

MySQL recursive queries provide a powerful mechanism to traverse and retrieve data from hierarchical structures. By utilizing recursive common table expressions (CTEs), we can build complex queries that handle various hierarchical scenarios. In this article, we explored the basics of creating MySQL hierarchical recursive queries with code examples. By applying these techniques, you can efficiently work with hierarchical data in your MySQL database.

Remember to adapt the queries to your specific database schema and adjust the column names accordingly. With the knowledge gained from this article, you can now start building powerful hierarchical queries in MySQL.

Published on May 19, 2023

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | recursive