0Pricing
SQL Academy · Lesson

Recursive CTEs for Hierarchies

Walk hierarchical data (org charts, threaded comments, graph traversal) with WITH RECURSIVE and stop conditions.

Why Recursive?

Plain SQL can't walk a tree of unknown depth: parents of parents, children of children. Recursive CTEs are the standard-SQL solution.

Anatomy

A recursive CTE has two parts joined by UNION ALL:

WITH RECURSIVE name AS (
  -- 1. Anchor query: seed rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: references the CTE itself
  SELECT ...
  FROM name JOIN ...
)
SELECT * FROM name;

All lessons in this course

  1. Scalar, Row, and Table Subqueries
  2. Correlated vs Non-Correlated Subqueries
  3. Common Table Expressions (WITH)
  4. Recursive CTEs for Hierarchies
← Back to SQL Academy