How Can Recursive Self-Joins Efficiently Retrieve Hierarchical Data in SQL Server?

Efficiently Retrieving Hierarchical Data with Recursive Self-Joins in SQL Server

SQL Server's recursive self-joins provide an effective method for navigating hierarchical data structures within a single table. This technique involves traversing the table's rows to gather related data based on hierarchical relationships. The following query demonstrates a streamlined approach:

<code class="language-sql">WITH q AS (
    SELECT *
    FROM mytable
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q;</code>

This query utilizes a recursive common table expression (CTE) named 'q'. The CTE starts by selecting all rows where ParentID is NULL, representing the top-level nodes in the hierarchy. Subsequently, it recursively joins the mytable with the CTE itself, adding child rows based on the ParentID and PersonID columns. This iterative process continues until all descendants are included.

Consider this sample mytable:

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2
5	YT	NULL
6	IS	5

Retrieving CJ's hierarchy (where PersonID = 1) yields:

PersonID	Initials	ParentID
1	CJ	NULL
2	EB	1
3	MB	1
4	SW	2

Similarly, EB's hierarchy (PersonID = 2) would return:

PersonID	Initials	ParentID
2	EB	1
4	SW	2

To maintain the hierarchical order, a modified query incorporating an ordering condition is beneficial:

<code class="language-sql">WITH q AS (
    SELECT m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
    FROM mytable m
    WHERE ParentID IS NULL
    UNION ALL
    SELECT m.*, q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
    FROM mytable m
    JOIN q ON m.parentID = q.PersonID
)
SELECT *
FROM q
ORDER BY bc;</code>

This enhanced query introduces a bc column to the CTE, representing the path from each node to the root. Ordering the results by bc ensures that the hierarchical structure, including sibling order, is preserved. The ORDER BY clause can be customized to adjust sibling ordering as needed.

The above is the detailed content of How Can Recursive Self-Joins Efficiently Retrieve Hierarchical Data in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!