Recursive Queries in SQL

Recursive queries in SQL are queries that involve self-referential relationships within a table. They allow you to perform operations that require iterative processing, enabling you to traverse and manipulate hierarchical data structures efficiently.

Recursive queries are SQL queries that can call themselves.

In SQL, recursion can be performed using Common Table Expressions (CTEs). CTEs are temporary result sets that can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement. Recursive CTEs are special kinds of CTEs that allow you to perform recursive tasks and queries.

The general structure of a recursive CTE is:

WITH RECURSIVE cte_name (column_name(s))

AS (

    -- Base case

    SELECT ...

    UNION ALL

    -- Recursive case

    SELECT ...

    FROM cte_name ...

)

 

SELECT * FROM cte_name;

Here,

1.      WITH RECURSIVE: This clause indicates that recursion is going to be used in the query.

2.      cte_name (column_name(s)): Here the name of  CTE  is defined and the names of the columns that it will contain.

3.      SELECT ... UNION ALL SELECT ...: This is the heart of recursion. The first SELECT statement is the base case(anchor), and the second SELECT statement is the recursive case. UNION ALL is used to combine the results of both.

4.      FROM cte_name ...: In the recursive SELECT statement, the CTE is referenced to itself.

5.      SELECT * FROM cte_name;: Finally, query the CTE to get our results.

Example:

WITH RECURSIVE employee_hierarchy AS (

    SELECT employee_id, employee_name, manager_id

    FROM employees

    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id

    FROM employees e

    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id

)

SELECT * FROM employee_hierarchy;