How Do Recursive CTEs Execute Line by Line: A Step-by-Step Explanation?

Execution of Recursive CTEs, Line by Line

Recursive Common Table Expressions (CTEs) are powerful SQL constructs that enable recursive queries. To understand how they execute, consider them as a series of UNION ALL operations.

Consider the provided example:

DECLARE @tbl TABLE ( 
      Id INT 
    , [Name] VARCHAR(20) 
    , ParentId INT 
);

Line 1-2: Define the table structure.

Line 3-16: Insert records into the table.

Line 19-24: Define the recursive CTE abcd.

Line 19: The anchor expression defines the initial iteration of the CTE by selecting records where ParentId is NULL.

Line 20-22: The recursive expression expands the CTE by selecting child records and concatenating their names with the parent's path in the Path column.

Line 25: Select from the CTE abcd to retrieve the final result.

Execution Steps:

1. Execute the anchor expression (line 19), which returns the following result:

Id	Name	ParentId	Path
1	Europe	NULL	Europe
2	Asia	NULL	Asia

2. Execute the recursive expression (line 20-22) using the result from the anchor expression. This yields:

Id	Name	ParentId	Path
3	Germany	1	Europe/Germany
4	UK	1	Europe/UK
5	China	2	Asia/China
6	India	2	Asia/India

3. Repeat step 2 until no more child records are found, resulting in:

Id	Name	ParentId	Path
7	Scotland	4	Europe/UK/Scotland
8	Edinburgh	7	Europe/UK/Scotland/Edinburgh
9	Leith	8	Europe/UK/Scotland/Edinburgh/Leith

4. Union all the results from each iteration to obtain the final result shown in the SELECT statement (line 25).

Why the Anchor is not Repeated:

The anchor expression defines the initial starting point for the recursion. While it is executed in each iteration, it is distinct from the recursive expression, which expands the result set. Thus, the anchor records are only included once.

The above is the detailed content of How Do Recursive CTEs Execute Line by Line: A Step-by-Step Explanation?. For more information, please follow other related articles on the PHP Chinese website!