Recursive query using a common table expression (CTE)

Advantages:
A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained.
In SQL Server 2005, when a query references recursive CTE, it is called "recursive query ". Recursive queries are usually used to return layered data, such as displaying an employee or item list scheme in an organizational chart (one or more components of the parent product, and those components may have child components, or other parent-level product components.
Recursive CTE can greatly simplify the Code required to run recursive queries in SELECT, INSERT, UPDATE, DELETE, or create view statements. In earlier versions of SQL Server, recursive queries usually require temporary tables, cursors, and logic to control recursive step flows. For more information about common table expressions, see use common table expressions.

Recursive CTE structure:
1. Call a routine. The first call to recursive CTE includes one or more CTE_query_definitions connected by the union all, UNION, except t, or INTERSECT operators. These query definitions form a benchmark result set of the CTE structure, so they are called "positioning point members ". CTE_query_definitions is considered as positioning point members unless they reference the CTE itself. ALL positioning Point member query definitions must be placed before the first recursive member definition, and the union all operator must be used to join the last positioning point member and the first recursive member.
2. recursive call of the routine. Recursive calls include one or more CTE_query_definitions connected by the union all operator that references the CTE itself. These query definitions are called recursive members ".
3. Terminate the check. The termination check is implicit. recursion stops when no rows are returned in a call.

The semantics of Recursive Execution is as follows::
1. Split the CTE expression into positioning point Members and recursive members.
2. Run the positioning point member to create the first call or benchmark result set (T0 ).
3. Run recursive members and use Ti as the input and Ti + 1 as the output.
4. Repeat Step 3 until an empty set is returned.
5. Return result set. This is the result of executing union all on T0 to Tn.

Sample Code (obtain all parent-level data including itself ):With get_dim_Region_id as (select * from dim_Region where id = 962
Union all
Select a. * from dim_Region a join get_dim_Region_id B on a. id = B. parentId
) Select * from get_dim_Region_idThe result is as follows: