Recursive Common Table Expressions (CTEs)

In SQL, recursion appears as a CTE with the RECURSIVE modifier. Using this modifier, we can refer to the query within its definition. This approach to problem-solving requires practical training to get used to. Hence, we will review three problems of different difficulty levels and their solutions using recursive approaches.

Problem 1: Number sequence

Assume that we need to generate a continuous sequence of numbers from $n$ to $m$ (inclusive). Unfortunately, no function in MySQL does this job for us, so we are left to ourselves. Though, recursion can help us out with this a lot. We use a number, i.e., $n$, as the seed to recursively increment this number up until and including $m$, generating a sequence along the way. The following query accomplishes this without requiring another table, thus functioning as a small drop-in for other queries: