Recursive Common Table Expressions (CTEs) in SQL Server can be combnied with other queries using the UNION or UNION ALL operators. This is typically done within the recursive CTE definition itself, where the anchor member and the recursive member are joined by a UNION ALL. The final result set of the CTE can then be used in an outer query with other JOIN, UNION, or INTERSECT operations.

Structure of a Recursive CTE with UNION ALL

WITH RecursiveHierarchy AS (
    -- Anchor member: The starting point
    SELECT
        EmployeeKey,
        FullName,
        SupervisorKey
    FROM
        Staff
    WHERE
        SupervisorKey IS NULL

    UNION ALL

    -- Recursive member: Joins back to the CTE
    SELECT
        s.EmployeeKey,
        s.FullName,
        s.SupervisorKey
    FROM
        Staff s
    INNER JOIN
        RecursiveHierarchy r ON s.SupervisorKey = r.EmployeeKey
)
SELECT * FROM RecursiveHierarchy;

Combining Recursive CTE Results with Other Tables The result of a recursive CTE is a standard rowset that can participate in further set operations. For example, you can join it with another table.

WITH OrgChart AS (
    SELECT
        ID,
        Name,
        ReportsToID
    FROM
        Employees
    WHERE
        ReportsToID IS NULL
    UNION ALL
    SELECT
        e.ID,
        e.Name,
        e.ReportsToID
    FROM
        Employees e
    INNER JOIN
        OrgChart oc ON e.ReportsToID = oc.ID
)
-- Use the CTE result in a JOIN
SELECT
    oc.Name AS EmployeeName,
    d.DepartmentName
FROM
    OrgChart oc
LEFT JOIN
    DepartmentInfo d ON oc.ID = d.ManagerID
ORDER BY
    oc.Name;

Using UNION to Combine Separate Hierarchies A UNION can be used to merge results from two different recursive CTEs or to combine a recursive result with a non-recursive query.

-- Get a combined list of all managers and their departments
WITH ManagementTree AS (
    SELECT EmployeeID, Name, ManagerID FROM Staff WHERE Title = 'Director'
    UNION ALL
    SELECT s.EmployeeID, s.Name, s.ManagerID
    FROM Staff s
    INNER JOIN ManagementTree mt ON s.ManagerID = mt.EmployeeID
)
SELECT EmployeeID, Name, 'Management' AS GroupType FROM ManagementTree
UNION
SELECT DepartmentID, DeptName, 'Department' AS GroupType FROM Departments;

Key points:

• The UNION ALL between the anchor and recursive members is mandatory for the CTE to be recursive.
• The final result set of the CTE can be used with JOIN, UNION, INTERSECT, or EXCEPT in an outer query.
• Ensure the recursive logic has a proper termination condition to preventt an infinite loop.