O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Recursive CTE

A recursive CTE is a CTE with a subquery that refers to its own name. The WITH clause must begin with WITH RECURSIVE. The recursive CTE subquery has two parts, seed query and recursive query, separated by UNION [ALL] or UNION DISTINCT.

Seed SELECT is executed once to create the initial data subset; recursive SELECT is repeatedly executed to return subsets of data until the complete result set is obtained. Recursion stops when an iteration does not generate any new rows. This is useful to dig into hierarchies (parent/child or part/subpart):

WITH RECURSIVE cte AS(SELECT ... FROM table_name /* seed SELECT */ UNION ALL SELECT ... FROM cte, table_name) /* "recursive" SELECT */ SELECT ... FROM cte;

Suppose you want to print ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required