Recursive Queries in MySQL 8

Sometimes you’ll encounter the need to perform recursive queries (queries that refer to themselves) and it was somewhat frustrating to find workarounds in previous versions of MySQL. MySQL 8, however, has introduced Common Tables Expressions (CTE) that create named temporary datasets and in doing so facilitates the ability to recursively query your data. Here’s a hopefully easy-to-follow example.

First let’s create a simple table to work with where each row can refer to a parent in another row:

 CREATE TABLE `items` (
   `id` INT NOT NULL,
   `parent_id` INT NULL,
 PRIMARY KEY (`id`));

 INSERT INTO `items` (`id`) VALUES ('1');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('2', '1');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('3', '2');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('4', '2');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('5', '2');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('6', '1');
 INSERT INTO `items` (`id`) VALUES ('7');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('8', '7');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('9', '8');
 INSERT INTO `items` (`id`, `parent_id`) VALUES ('10', '8');

This “items” table contains the following data:

id	parent_id
1	NULL
2	1
3	2
4	2
5	2
6	1
7	NULL
8	7
9	8
10	8

This items in this table can be understood to represent the following tree structure:

CTE introduces the WITH clause. Each subquery in the WITH clause generates a result set. Let’s say you wanted your query to return all of the descendants of item 1. First you would need to find all of the items that have 1 as their parent item; in our example 2 and 6. Then you would also need to find all of the items that have either 2 or 6 as their parent; in this case 3, 4, and 5. Here’s an example that returns the descendants of item 1:

 WITH RECURSIVE item_cte (id, parent_id) as (
	
   SELECT id, parent_id
   FROM items
   WHERE id = 1
				
   UNION ALL
				
   SELECT i.id, i.parent_id
   FROM items i
   INNER JOIN item_cte ON i.parent_id = item_cte.id
				
 )			
 SELECT * FROM item_cte;

The first subquery grabs the first row of the CTE. The second subquery pulls additional additional rows and adds them using UNION ALL; this is recursive because it refers to the CTA (item_cte) in its FROM clause. This recursion continues until no new rows are available. The query above will return items 1, 2, 6, 3, 4, and 5.

Similarly, querying for 8 will return items 8, 9, and 10.

 WITH RECURSIVE item_cte (id, parent_id) as (
	
   SELECT id, parent_id
   FROM items
   WHERE id = 8
				
   UNION ALL
				
   SELECT i.id, i.parent_id
   FROM items i
   INNER JOIN item_cte ON i.parent_id = item_cte.id
				
 )			
 SELECT * FROM item_cte;

This is a very powerful tool that makes MySQL much better suited for querying data structures like linked lists and trees. The example above is a basic but common use case, but there are plenty of details beyond the scope of this article that you may want to familiarize yourself with: using UNION DISTINCT instead of UNION ALL to remove duplicate rows, setting the maximum recursion depth, things like that. Explore the docs here. Happy querying!