Skip to content

SQL Recursive Join (The Tree) 🌳

Mentor's Note: A SELF JOIN can find your Manager. But what if you want to find your Manager's Manager, and their Manager, all the way to the CEO? You need a loop. In SQL, that loop is a Recursive CTE. 💡

🌟 The Scenario: The Family Tree 🧬

  • Step 1 (The Anchor): Find the Grandfather.
  • Step 2 (The Loop): Find everyone who calls him "Father".
  • Step 3 (The Repeat): Find everyone who calls them "Father".
  • Step 4 (Stop): When no more children are found.

💻 The Basic Syntax

WITH RECURSIVE OrgChart AS (
    -- ⚓ Anchor Member (The CEO)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 🔄 Recursive Member (The Loop)
    SELECT E.id, E.name, E.manager_id, O.level + 1
    FROM employees E
    INNER JOIN OrgChart O ON E.manager_id = O.id
)
SELECT * FROM OrgChart;

🎨 Visual Logic: The Loop

graph TD
    A[Start: Anchor (Level 1)] --> B{Find Children?}
    B -- Yes --> C[Add to List (Level +1)]
    C --> B
    B -- No --> D[Stop & Return Result]

💡 Practical Use Cases

  1. Menu Systems: Category -> Sub-Category -> Product.
  2. Manufacturing: Product -> Part -> Sub-Part -> Screw.
  3. Social Networks: Friend -> Friend of Friend (Degrees of Separation).

📈 Learning Path