What is the flow of control of recursive cte methods in SQL?

Question

I have a query to solve a question:

with recursive cte as(
    select employee_id from employees where manager_id = 1 and employee_id != 1
    union all
    select a.employee_id from employees a inner join cte b on b.employee_id=a.manager_id)

select employee_id from cte

I read the documentation and understood that the first line is the anchor member, the second line joins the result sets, third line is the subquery that references to the cte and hence, the recursion.

What I did not understand was the flow of control. For eg, a cte named cte is declared, we run a select query (the anchor), then we perform a union all with another result set that recursively calls this cte.

It is stated in the documentation that the recursion terminates once the result set is empty. I am not able to understand at what step of the flow will the recurion terminate.

Answer 1

It is stated in the documentation that the recursion terminates once the result set is empty. I am not able to understand at what step of the flow will the recurion terminate.

Let's have a look at the recursive part of the CTE:

select a.employee_id 
from employees a 
inner join cte b on b.employee_id = a.manager_id

When does this return no rows? When there is no employee whose manager_id is equal to the employee_id of cte.

Basically, the query walks a hierarchical structure: at each step, it brings the managees of the current employee. When the current employee is not managing any employee, the recursive part comes back empty, and the recursion stops.