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.

Structure of a Recursive CTE

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.

Syntax

The basic syntax for a recursive CTE is as follows:

Recursive CTE syntax

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.

Example: Referral Chain

The following query traces the full referral chain starting from the root user who was not referred by anyone.

referrals

user_id	name	referred_by
1	Sofia	NULL
2	Ethan	1
3	Aisha	2
4	Noah	2
5	Mia	3

Recursive CTE Example

-- 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;

Output:

user_id	name	referred_by	depth
1	Sofia	NULL	1
2	Ethan	1	2
3	Aisha	2	3
4	Noah	2	3
5	Mia	3	4

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

Notes

• 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

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In