Recursive CTEs

A Recursive CTE is a Common Table Expression that refers to itself.

They are useful for working with hierarchical relationships (such as user referrals or management structures) and for repeated logic (such as sequence generation).

In this lesson, we’ll see how recursive CTEs handle hierarchical or self-referencing data.

A recursive CTE has two parts:

1. Anchor member: the base case (starting rows)
2. Recursive member: a query that builds upon the anchor by referring to the CTE itself

Both parts are joined using UNION ALL.

The basic syntax for a recursive CTE is as follows:

Copy
WITH RECURSIVE cte_name AS (
  -- Anchor member
  SELECT ...

  UNION ALL

  -- Recursive member
  SELECT ...
  FROM cte_name
  JOIN ...
  ON ...
)
SELECT * FROM cte_name;

The recursion continues until no new rows are produced.

The following query traces the full referral chain, starting with the root user who has no referrer.

referrals

Copy
-- Recursive CTE to trace referral chain starting from Sofia
WITH RECURSIVE referral_chain AS (
  SELECT user_id, name, referred_by, 1 AS depth
  FROM referrals
  WHERE referred_by IS NULL

  UNION ALL

  SELECT r.user_id, r.name, r.referred_by, rc.depth + 1
  FROM referrals r
  JOIN referral_chain rc ON r.referred_by = rc.user_id
)
SELECT *
FROM referral_chain
ORDER BY depth;

This query returns all users directly or indirectly referred by Sofia, along with their level in the referral chain.

• Prefer UNION ALL unless you specifically need to remove duplicates
• Always ensure a stopping condition exists (e.g. finite child rows)
• Not all databases support recursive CTEs, so check your SQL engine’s compatibility.