Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
An ordinary CTE reads tables. A recursive one reads itself, so each round of rows produces the next. It is how SQL walks a hierarchy or generates a series, neither of which a plain SELECT can do. Open the client with mysql -u root -p sakila before you start.

​
How a recursive CTE works

Replace CTE_NAME with a name for the result, COLUMN_NAME with the column it produces, SEED_VALUE with the starting value, NEXT_EXPRESSION with what makes the next row from the previous one, and STOP_CONDITION with the test that eventually fails. A recursive CTE has 2 kinds of part, joined by UNION ALL:
  • A seed, also called the anchor member, which mentions the CTE nowhere and produces the starting rows.
  • A recursive member, which selects from the CTE itself.
Most have exactly 1 of each, which is the shape below. MySQL accepts more than 1 of either, and accepts UNION in place of UNION ALL, which deduplicates each round. Neither is common and both are worth recognizing when you meet them. The server runs the seed, then runs the recursive part against whatever the previous round produced, over and over, until a round produces no rows. The WHERE in the recursive part is what eventually makes that happen. The keyword is WITH RECURSIVE, and it goes on the WITH, not on the CTE.

​
Counting to 5

The seed makes row 1. The recursive part takes that row and makes 2, then takes 2 and makes 3, and stops when it reaches 5 because WHERE n < 5 produces no next row. A series like this is more useful than it looks. Joined against real data it gives you a row for every day in a range, including the days nothing happened, which no query over the data alone can produce.

​
Leaving out the stop condition

Drop the WHERE and the recursive part always produces another row.
The server stops it rather than running forever. The limit is a setting:
The count in the message is 1 higher than the setting, because the round that breaches the limit is counted before the server stops. Read ERROR 3636 as a missing or wrong stop condition first. Raising the limit with SET SESSION cte_max_recursion_depth = 5000; is right only when you genuinely need more rounds than the default allows. It is wrong when the query would never have stopped, because it then takes longer to fail and fails anyway. The setting is a variable, so the 1,000 above is this server’s value rather than a fixed property of MySQL.

​
Summary

  • Write WITH RECURSIVE when a CTE reads itself.
  • Give it a seed part and a recursive member, joined by UNION ALL.
  • Put the stop condition in the recursive part’s WHERE.
  • Read ERROR 3636 as a runaway recursion before reaching for the setting.

​
See also