Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Summary for Recursion

Understand how recursion works in MySQL through recursive common table expressions. Learn the differences between UNION and UNION ALL in recursion, and discover ways to optimize recursive queries, including limiting recursion depth and execution time, to enhance performance and avoid infinite loops.

We'll cover the following...

Recursion is a phenomenon in which something is defined in terms of itself. In computer science, recursion refers to a function that calls itself as part of its definition. In the sense of divide and conquer, recursion allows one to solve a complex problem in smaller, simple steps.

Recursive common table expressions (CTEs)

In MySQL, recursion is implemented through recursive CTEs:

MySQL
WITH [RECURSIVE]
  cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

With RECURSIVE, we are allowed to refer to cte_name in the definition of subquery. As recursion can incur an infinite loop, we also need to define a condition to terminate the recursive ...