Why should you care?

Show all subcategories under “Electronics” on DataFlow catalog menu — recursion expands tree in one SQL call.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

WITH RECURSIVE cat_tree AS (
  SELECT category_id, name, parent_id, 0 AS depth
  FROM categories WHERE category_id = 1
  UNION ALL
  SELECT c.category_id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN cat_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM cat_tree ORDER BY depth, name;

What happened?

• Anchor picks root category 1.
• Recursive part attaches children whose parent_id matches row already in cat_tree.
• depth tracks level; stops when no new children.

Practice next

1. CREATE categories with parent_id self-FK.
2. Insert 3-level tree.
3. Run recursive CTE from root id 1.
4. Start from leaf upward using different join direction.
5. cte_max_recursion_depth system variable if deep tree.

Remember

UNION ALL links anchor + recursive parts. Great for trees and graphs with parent pointers. Guard depth or cycle detection in data.

DataFlow category breadcrumb

API builds breadcrumb trail from recursive CTE to root for SEO URLs.

Outcome: Single query replaces recursive app code.