In the world of computer science, recursion takes place when we invoke a block of instructions inside itself.
In SQL, we talk about a recursive CTE when that CTE refers to itself. The idea behind recursive CTEs is usually as follows: 'Hey, give me everything you have so far, but add something extra as well. Then, repeat this procedure until I tell you to stop. By adding small pieces in each recursive step, I expect to finally arrive at the right result.'
Sounds vague? Let's introduce our first example.
Let's say we want to show numbers from 1 to 10, but we don't have any table with rows containing those numbers.
Luckily, we can use recursion. Take a look at the template, run it and see the result. Don't pay too much attention to the details – we'll explain everything in a second.
