Recursive Query

What is a recursive query?

A recursive query is a query that refers back to itself.

Why do we use it?

It allows you to repeatedly execute a part of a query while changing some condition until it reaches a stopping point.

How do we build a recursive query?

Recursive queries use a common table expression (CTE) to define the recursive part of the query. This is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

WITH RECURSIVE cte_name(column_list) AS (
    -- Anchor member
    SELECT ...
    FROM ...
    UNION [ALL] // combine the anchor and recursive members,
    -- Recursive member
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT ...
FROM cte_name
WHERE ... // specify the termination condition for the recursion.

The query consists of two parts: the anchor member and the recursive member.

Anchor Member

The anchor member is the initial part of the query, which returns the base case or starting point for the recursion

Recursive Member

The recursive member is the part of the query that refers to the CTE and is executed repeatedly until a termination condition is met.