Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Recursive Tree

Recursive Tree

Learn to use SQL queries to traverse hierarchical data like referral trees or organizational charts.

We'll cover the following...

Every modern business has to deal with hierarchical data.

Think about an organization chart, product categories with subcategories, or a customer referral network. Imagine we want to find all the people referred directly or indirectly by a particular customer. This kind of recursive relationship is not something we can easily handle with a standard join or filter.

That is where the recursive tree pattern comes in.

In this lesson, we’ll learn how to query hierarchical data using recursive common table expressions (CTEs). We’ll break down the structure of recursive queries, learn how to think in terms of tree traversal, and write SQL that can drill through multiple levels of a relationship.

By the end, we’ll be able to answer complex questions about parent-child chains and multilevel hierarchies in a clean and scalable way.

In this lesson, we will:

  • Understand when and why to use recursive queries in SQL.

  • Learn the structure of a recursive CTE and how it works step-by-step.

  • Use recursive queries to explore hierarchical relationships, such as customer referral chains.

  • Write, read, and debug recursive SQL queries confidently.

Pattern overview

Category:

  • Sequencing & Hierarchical Patterns

Intent:

To retrieve hierarchical, parent-child, or tree-structured data using recursive queries.

Motivation:

Hierarchical relationships are everywhere in databases: employee-manager structures, referral systems, category-subcategory setups, and more. These relationships can't be fully explored with a simple join, especially when they go multiple levels deep. We need a pattern that can expand a tree-like structure as far as it goes. Recursive queries help us follow these chains cleanly and efficiently.

Also known as:

  • Hierarchical queries

  • Tree traversal

  • Parent-child expansion

  • Recursive CTEs

Structure

WITH RECURSIVE TreeCTE (id, parent_id, level) AS (
    -- Anchor member: starting point
    SELECT id, parent_id, 1 AS level
    FROM   SomeTable
    WHERE  parent_id IS NULL  -- or other base condition
    UNION ALL
    -- Recursive member: self-join on CTE
    SELECT     child.id, child.parent_id, level + 1
    FROM       SomeTable AS child
    INNER JOIN TreeCTE AS parent ON child.parent_id = parent.id
)
SELECT * FROM TreeCTE;
Syntax for Recursive tree pattern

Keywords

WITH RECURSIVE, UNION ALL, anchor query, recursive query, hierarchy, parent-child, tree traversal

Problem structure

We use the Recursive Tree pattern when:

  • We need to traverse hierarchical or tree-structured data (e.g., categories, org charts, comment threads).

  • We start with a base case (anchor query) that selects the top-level or root nodes.

  • We use a recursive query that repeatedly joins the base result with itself to move through levels of the hierarchy.

  • The recursion continues until no more child records are found.

  • Look for keywords like: “hierarchy,” “levels,” “parent-child,” “nested structure,” or “walk the tree” to identify when this pattern applies.

Example use case: Customer referral network

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID.

The table contains information about customers. Let’s say we want to find all customers who were referred directly or indirectly by a specific customer (e.g., a customer with CustomerID = 1). We’ll use the Customers table, where the ...