Understanding Recursive CTE- Simple Example

Let me help you relate the word "recursive" to python for a second. Recursion in its every essence means when a function calls itself repeatedly until a termination condition is met, so a basic factorial code calls the factorial function within itself until the base condition and then unfolds from that base condition, consolidating the result as it unfolds. Stay with me- a recursive cte is way simpler! It just takes or considers the previous row in the table and performs whatever function you want it to do. It would be clear with a simple example as follows.

Printing a table with rows counting from 1 to 5.

with cte as (
select 1 as Number     ---> anchor part
union all
select Number+1       ---> recursive part
from cte 
where Number<5
)
select * from cte        

The anchor part initializes the first row of your table, in this case, its a simple "1" as the first row of your cte. Visualize as follows:

|Number |
 | 1 |        

Now, this number column with the first row is stored in your cte, and now things get interesting with the recursive part. When we do union all with the recursive part, the first iteration takes the only row of your anchor part stored in the cte, checks where the number which is equal to 1 is less than 5 or not. In this case it is, so it increments it by 1 and adds another rows. Now your cte as has two rows, one from the anchor part, and the other from the union of the 1st iteration of the recursive part. The result now looks like this:

| Number | 
|----------|
|     1 |         -->anchor result
|     2 |        --> recursive part starts here        

Ok, the first iteration of your anchor part is now complete. We go again, the (select number+1) takes the value from the cte table now which has 2 as the latest row, checks whether its less than 5 and then increments.

| Number |
|--------|
|     1  |
|     2  |
|     3  |        

And this repeats till the number becomes 5, and 5 is not less than 5. Remember that, the "where" condition is evaluated first before the increment takes places, that's why the where condition has where number <5.

So when Number = 5, the where condition does not get satisfied, and the recursion terminates. The table now looks like this.

| Number |
|--------|
|     1  |
|     2  |
|     3  |
|     4  |
|     5  |        

Easy right? It simply increments the value from its previous row!