Recursive CTEs

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

It's useful when working with hierarchical relationships (like user referrals or management structures) or repeated logic (like sequence generation).

In this lesson, we'll look at how to use recursive CTEs to work with 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 from the root user who was not referred by anyone.

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 returns all users directly or indirectly referred by Sofia, along with their depth 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 — check your SQL engine's compatibility