Course contentsShow
← Data Science
Lesson 996 of 2,14525. SQL Subqueries and CTEsFree lesson

Recursive CTEs: Introduction

Understanding recursive CTEs for hierarchical data like organizational charts, using anchor and recursive members.

Recursive CTEs: Introduction

You'll learn: How recursive CTEs repeatedly reference themselves to traverse hierarchical or tree-like data structures.

What Makes a CTE Recursive?

Until now, every CTE you've written has been "non-recursive"—it defines a result set once and you're done. But what if your data naturally forms a hierarchy, like an employee reporting chain or a folder tree? A recursive CTE solves this by defining a query that calls itself, building results layer by layer until there's nothing left to add.

Think of it like climbing a family tree: you start with one person (the "anchor"), then find their parents (first recursion), then their parents (second recursion), and so on until you reach the top.

Two Essential Parts

Every recursive CTE has exactly two components:

  1. Anchor Member: The starting point—your initial row(s) with no dependencies. Example: the CEO who reports to no one.
  2. Recursive Member: The self-referencing query that adds the next "layer" by joining back to what you've already found. Example: find employees whose manager is someone already in your result set.

These two parts are connected with UNION or UNION ALL. The database alternates: run the anchor once, then run the recursive part repeatedly until it returns no new rows.

Basic Structure (Concept Only)

WITH RECURSIVE hierarchy_name AS (
  -- Anchor: starting point
  SELECT ... 
  
  UNION ALL
  
  -- Recursive: reference hierarchy_name itself
  SELECT ... 
  FROM hierarchy_name
  JOIN ...
)
SELECT * FROM hierarchy_name;

You're not writing runnable code yet—just understand that the CTE's name appears inside its own definition.

Key Takeaway: Recursive CTEs use an anchor member (starting rows) and a recursive member (self-referencing query) to traverse hierarchical data step-by-step until complete.