Recursive Queries

Code

-- Recursive query for org chart
WITH RECURSIVE org_tree AS (
    -- Base query: top-level manager
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive query: subordinates
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, level,
    REPEAT('  ', level - 1) || name AS tree_view
FROM org_tree
ORDER BY level, name;

-- Recursively generate sequence
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

Explanation

Recursive CTE consists of a base query and recursive query, suitable for tree and hierarchical data.

More SQL Snippets