Aprenda Recursive CTEs: Concepts and Syntax | Recursive CTEs and Hierarchical Data

Deslize para mostrar o menu

Recursive Common Table Expressions (CTEs) provide a powerful way to handle hierarchical and tree-structured data within SQL. Unlike standard CTEs that simply encapsulate a subquery, recursive CTEs allow you to repeatedly execute a query, feeding the results of each iteration back into the next. This is essential when working with data that references itself, such as organizational charts, category trees, or task dependency chains. A recursive CTE is composed of two main parts: the anchor member and the recursive member. The anchor member produces the base result set, while the recursive member repeatedly references the CTE itself to build upon the previous results. Recursive CTEs are especially useful for traversing hierarchical data, such as finding all subordinates of a manager, expanding product categories, or resolving task dependencies in project management scenarios.


              1234567891011121314151617181920212223242526272829303132
            
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: start with the CEO (no manager)
    SELECT
        employee_id,
        name,
        position,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: find employees who report to the previous level
    SELECT
        e.employee_id,
        e.name,
        e.position,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
        ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    name,
    position,
    manager_id,
    level
FROM employee_hierarchy
ORDER BY level, manager_id, employee_id;

In the recursive CTE above, the anchor member selects the CEO, who has no manager. The recursive member then joins the employees table with the results of the previous iteration, finding employees who report directly to those already identified. This process repeats, each time adding another level to the hierarchy, until no more employees remain to be linked. The recursion terminates naturally when the recursive member produces no new rows, meaning all employees have been included. This step-by-step expansion is what enables recursive CTEs to walk through hierarchical relationships, building a complete path from the root to every leaf in the structure.


              12345678
            
WITH RECURSIVE numbers AS (
    -- Anchor member: start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive member: increment n by 1 until 10
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

1. What are the two main parts of a recursive CTE?

2. In what scenarios are recursive CTEs especially useful?

3. Given the following recursive CTE, identify which part is the anchor member and which is the recursive member:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth
    FROM product_categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1
    FROM product_categories c
    INNER JOIN category_tree ct
        ON c.parent_category_id = ct.category_id
)
SELECT * FROM category_tree;

Match the following code segments to their correct role:

SELECT
    category_id,
    category_name,
    parent_category_id,
    1 AS depth
FROM product_categories
WHERE parent_category_id IS NULL

SELECT
    c.category_id,
    c.category_name,
    c.parent_category_id,
    ct.depth + 1
FROM product_categories c
INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id

Given the following recursive CTE, identify which part is the anchor member and which is the recursive member:

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        1 AS depth
    FROM product_categories
    WHERE parent_category_id IS NULL

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1
    FROM product_categories c
    INNER JOIN category_tree ct
        ON c.parent_category_id = ct.category_id
)
SELECT * FROM category_tree;

Match the following code segments to their correct role:

SELECT
    category_id,
    category_name,
    parent_category_id,
    1 AS depth
FROM product_categories
WHERE parent_category_id IS NULL

SELECT
    c.category_id,
    c.category_name,
    c.parent_category_id,
    ct.depth + 1
FROM product_categories c
INNER JOIN category_tree ct
    ON c.parent_category_id = ct.category_id

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 2. Capítulo 1

Pergunte à IA

Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo

Seção 2. Capítulo 1