Fancy way of saying recursively fetching data.
- It is far more efficient than looping when there are a large no of branches. Recursion seamlessly handles branching paths
It performs a subquery on each iteration until the subquery returns zero rows. If the subquery returns rows those returned rows as placed into a workarea whereby they comprise the contents of the virtual cte table during the next iteration. They are also placed into the final result workarea, which is what gets returned when the CTE is finished being evaluated. Pass: virtual cte table : execution result
Pass 1: <empty : main query (2 rows)
Pass 2: <2 rows> : iteration query (3 rows)
Pass 3: <3 rows> : iteration query (1 row)
Pass 4: <1 row> : iteration query (0 rows)
Done: Returns 6 rows The iteration query (below the union all) basically uses the virtual cte table as a parent to determine the next set of children, which then themselves becomes parents, etc… The query above the union initializes the first set of parents.
The fundamental difference between recursion and looping is recursion will seamlessly handle branching paths - if your top level object has 5 children, and each of those objects has 5 children, there are 25 independently scoped copies of that query (or function) at n-2, and more importantly it can keep going deeper. Something that is much harder to achieve with loops.
WITH RecursiveFactorial AS (
-- Anchor Member
SELECT 1 AS n, 1 AS factorial
UNION ALL
-- Recursive Member
SELECT n + 1, factorial * (n + 1)
FROM RecursiveFactorial
WHERE n < 10 -- Termination Condition
)
SELECT * FROM RecursiveFactorial;