What makes a CTE recursive?

Question

Accepted Answer

A recursive CTE has two parts separated by UNION ALL: the anchor (base case) that runs once to seed the result, and the recursive member that joins back to the CTE itself to extend it. The database keeps running the recursive member until it produces no new rows. This pattern is how you traverse hierarchical data like org charts or bill-of-materials trees without knowing the depth in advance.