Understanding Recursive Queries in MySQL with Practical Examples

Overview

Recursive query is a technique for handling hierarchical data in databases by referencing the query itself.

In MySQL it can be implemented with the WITH RECURSIVE statement, which allows defining a recursive query that references itself.

Recursive queries usually contain two parts: the base query and the recursive query .

The base query is the starting point that returns the initial result set.

The recursive part defines how to continue querying the next level from the previous result set until a termination condition is met.

Note: MySQL introduced this window‑function capability only in version 8.0 , so it is a new feature of MySQL 8.

Structure

Recursive queries usually include the following key elements:

Initial Query (Anchor Query) : the entry point that returns the first result set.

Recursive Query : the core part that references itself and defines how to fetch the next level from the previous level.

Termination Condition : the condition that stops the recursion, usually based on a data‑driven limit.

Execution Process

The execution steps are:

Run the base query to obtain the initial result set.

Feed the initial result set into the recursive query, combine the results, and repeat.

Continue until the termination condition is satisfied.

Application Scenarios

Recursive queries are useful for any hierarchical or tree‑structured data, such as organizational charts, category trees, path queries, and hierarchical calculations.

Note: The examples below are just common scenarios; recursive queries can be applied to any data with a hierarchical structure.

Case Study: Organizational Hierarchy

A simple organization table is created to demonstrate recursive queries.

5.1 Create the organization table

CREATE TABLE `organization` (
  `org_id` int NOT NULL COMMENT '主键',
  `org_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织名称',
  `parent_id` int DEFAULT NULL COMMENT '父组织id',
  `org_level` int DEFAULT NULL COMMENT '组织级别',
  PRIMARY KEY (`org_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `organization_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `organization` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='公司组织架构';

5.2 Insert sample data

INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (1,'集团总部',NULL,1);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (2,'华北分公司',1,2);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (3,'华南分公司',1,2);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (4,'华北-北京公司',2,3);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (5,'华北-内蒙公司',2,3);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (6,'华南-广州公司',3,3);
INSERT INTO `organization`(`org_id`,`org_name`,`parent_id`,`org_level`) VALUES (7,'华南-深圳公司',3,3);

5.3 Recursive query with pagination

WITH RECURSIVE RecursiveOrganization AS (
  SELECT org_id, org_name, parent_id, org_level
  FROM organization
  WHERE parent_id IS NULL -- root nodes
  UNION ALL
  SELECT o.org_id, o.org_name, o.parent_id, o.org_level
  FROM organization o
  INNER JOIN RecursiveOrganization ro ON ro.org_id = o.parent_id
)
SELECT org_id, org_name, parent_id, org_level
FROM RecursiveOrganization
ORDER BY org_id
LIMIT 2 OFFSET 0; -- pagination

The query works as follows:

The WITH RECURSIVE clause creates a view named RecursiveOrganization . The base part selects rows where parent_id IS NULL (root nodes).

The UNION ALL part joins the view with the original table to fetch child rows recursively.

The final SELECT retrieves the hierarchical data, ordered by org_id , and the LIMIT / OFFSET clause implements pagination.

Conclusion

Recursive queries are powerful for handling parent‑child, tree, or hierarchical data, allowing easy retrieval of all levels regardless of depth. They can also be used for pagination, path queries, and hierarchical calculations.

However, recursive queries may consume significant resources and can impact performance on large data sets, so careful design and optimization are required.