Definition

WITH RECURSIVE is an advanced SQL query construct that executes recursive queries. Recurisve queries repeatedly apply a rule or algorithm to incrementally build a result set, often solving hierarchical or tree-structured data traversal challenges.

Key Components of WITH RCEURSIVE

1. Common Table Expression (CTE)

The WITH keyword introduces one or more CTEs. The RECURSIVE keyword indicates at least one CTE is recursive. The CTE name (recursive_cte_name) identifies the temporary result set, and an optional column list defines the structure. The initial query (base_query) provides the recursion’s starting point, and the recursive subquery defines how previous iteration results generate next-step data.

2. Recursive Query Structure

A recursive query consists of two parts joined by UNION or UNION ALL:

• Base Query: Fetches boundary data as the recursion starting point (top-level nodes).
• Recursive Subquery: Uses prior CTE results to compute new data. It references the CTE name, applying the same logic iteratively.

3. Joining Operator

Use UNION to deduplicate results or UNION ALL to preserve all duplicates, choosing based on requirements.

4. Termination Condition

A clear termination condition (often a WHERE clause or join constraint) is required to prevent infinite recursion. When no new data is generated in an iteration, recursion stops.

Example

Suppose we have a staff table storing employee-manager relationships:

CREATE TABLE staff (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_emp_id INT,
    FOREIGN KEY (manager_emp_id) REFERENCES staff(emp_id)
);

To query an employee hierarchy with levels, use this WITH RECURSIVE statement:

WITH RECURSIVE staff_hierarchy AS (
    -- Base query: Fetch top-level managers without a manager
    SELECT emp_id, emp_name, manager_emp_id, 1 AS depth
    FROM staff
    WHERE manager_emp_id IS NULL
    UNION ALL
    -- Recursive subquery: Fetch next-level employees
    SELECT s.emp_id, s.emp_name, s.manager_emp_id, sh.depth + 1
    FROM staff s
    JOIN staff_hierarchy sh ON s.manager_emp_id = sh.emp_id
)
SELECT * FROM staff_hierarchy;

In this example:

• staff_hierarchy is the CTE name.
• The base query retrieves employees with manager_emp_id = NULL.
• The recursive subquery joins staff_hierarchy with staff to find direct reports, encrementing depth each iteration.
• No explicit termination condition is needed—recursion stops when no more direct reports are found.

This structure works for traversing organizational charts, directory structures, pathfinding, or generating number sequences, simplifying complex logic and improving readability.

Additional Use Case

Here’s a query for counting staff in each sub-department (filtered to specific root departments) from an organizational element table:

WITH RecursiveOrgCTE (org_id, org_type, parent_id, org_name, root_dept) AS (
    SELECT
        so.fd_id,
        so.fd_org_type,
        so.fd_parentid,
        so.fd_name,
        sp.fd_name
    FROM sys_org_element so
    LEFT JOIN sys_org_element sp ON sp.fd_id = so.fd_parentid
    WHERE so.fd_parentid IN (
        '17d74c2814c23bd3f948d234ee49f27a',
        '17d74c2825131a0e4e1e53b4b44bb06e',
        '17d74c2846210bbb36c4b0c44ef83fca',
        '17d74c2856e81e4449454a14256b5dc7',
        '17d74c2867335f1481cc16647db9d627',
        '17d74c28a72880a4dae747e40299ce54',
        '17d74c28b7347e4391af64d40ceac81a',
        '17d74c291eeae82423cd82f414184f42',
        '17d74c292fe14bc5dd103df42dba534c',
        '17d74c299e457ef55c597ba46c9b3aa6',
        '17e66eb369a55a1c30f0cad42e19e391',
        '17e6be078d4bd690c7187fc48968b444',
        '186d9e62b4eeeebe356249448f68ef7e',
        '186d9e90262b8d2b1fdee6145d296cf6',
        '186d9e9ad140ec8d4b230ac4cda9199f',
        '186d9f7f752ff30243cb662433e844ad',
        '18cae99b195f61c0efbef874da288f7b',
        '17d74c2876c8cff26cbfa4c4f1499b95'
    )
    UNION ALL
    SELECT
        so1.fd_id,
        so1.fd_org_type,
        so1.fd_parentid,
        so1.fd_name,
        roc.root_dept
    FROM sys_org_element so1
    JOIN RecursiveOrgCTE roc ON so1.fd_parentid = roc.org_id
)
SELECT
    roc.root_dept AS department,
    COUNT(roc.root_dept) AS staff_count
FROM RecursiveOrgCTE roc
LEFT JOIN hr_staff_person_info hri ON hri.fd_org_person_id = roc.org_id
LEFT JOIN sys_org_element so2 ON so2.fd_id = roc.parent_id
WHERE roc.org_type = 8
GROUP BY roc.root_dept;