Recursive CTEs & Hierarchical Data — SQL Course

What you'll learn

Traverse trees and graphs with anchor and recursive terms, track depth and paths, and prevent cycles. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply WITH RECURSIVE to a realistic data question
Apply Anchor term to a realistic data question
Apply Recursive term to a realistic data question
Apply Depth and path to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Anchor term	The initial rows of recursion	Select the exact root or starting frontier
Recursive term	The step joining prior output to new rows	Guarantee progress and bounded growth
Cycle detection	Preventing repeated nodes in a graph	Track a path or use database cycle syntax when data may loop

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the recursive hierarchy traversal question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Traverse a category tree

The path supports readable order and prevents revisiting an ancestor.

category_tree.sql

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth, ARRAY[id] AS path
  FROM catalog.categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM catalog.categories AS c
  JOIN tree AS t ON c.parent_id = t.id
  WHERE NOT c.id = ANY(t.path)
)
SELECT id, repeat('  ', depth) || name AS label
FROM tree ORDER BY path;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A recursive term without cycle protection or progress conditions can repeat forever or explode the working set.

Independent workshop

Build a review-ready recursive hierarchy traversal lab against the course commerce dataset.

Your finished workshop must include:

WITH RECURSIVE
Anchor term
Recursive term
Depth and path
Cycle prevention
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Anchor term: Select the exact root or starting frontier
Recursive term: Guarantee progress and bounded growth
Cycle detection: Track a path or use database cycle syntax when data may loop

Quick check

1. Which rule best applies to Anchor term?

2. Which rule best applies to Recursive term?

3. Which rule best applies to Cycle detection?

Next: Window Functions: Ranking & Partitions