Baldwin Apps

Posted on Jun 30

SQL Pattern Series #10: The Hierarchy Pattern

#sql #database #beginners #tutorial

Relating rows in a table to other rows in the same table

SQL Pattern Series #10 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a self-join is
How one table can represent hierarchical relationships
How to connect employees to managers
When to use the Hierarchy Pattern

Most joins connect one table to another table.

For example:

Orders
JOIN Customers

But sometimes the relationship exists inside the same table.

That is where the Hierarchy Pattern becomes useful.

The Problem

Imagine an Employees table:

EmployeeID	EmployeeName	ManagerID
1	Alice	NULL
2	Bob	1
3	Carla	1
4	Diego	2

The employee and the manager are both stored in the same table.

Bob is an employee.

Alice is also an employee.

But Alice is Bob's manager.

So the table needs to relate one row to another row within itself.

The Hierarchy Pattern

The Hierarchy Pattern uses a table more than once in the same query.

This is usually called a self-join.

The key idea is:

Treat the same table as if it were two different roles.

For example:

FROM Employees e
JOIN Employees m
    ON e.ManagerID = m.EmployeeID

Here:

e represents the employee
m represents the manager

Both aliases point to the same table.

But each alias plays a different role in the query.

Example

SELECT
    e.EmployeeID,
    e.EmployeeName,
    m.EmployeeName AS ManagerName
FROM Employees e
JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

This query returns each employee with their manager's name.

The join connects:

e.ManagerID

to:

m.EmployeeID

Conceptually, SQL asks:

Which employee row points to another employee row as its manager?

Example Result

EmployeeID	EmployeeName	ManagerName
2	Bob	Alice
3	Carla	Alice
4	Diego	Bob

Notice that Alice does not appear in this result.

Why?

Because Alice has no manager.

Her ManagerID is NULL, so the inner join does not find a matching manager row.

Keeping Top-Level Rows

If you want to include employees who do not have managers, use a LEFT JOIN:

SELECT
    e.EmployeeID,
    e.EmployeeName,
    m.EmployeeName AS ManagerName
FROM Employees e
LEFT JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

This keeps every employee.

Employees without managers return NULL for ManagerName.

This is useful for top-level roles such as:

CEO
founder
department head
root category

Why This Pattern Matters

Hierarchical relationships appear all over real systems.

Examples include:

employees and managers
categories and parent categories
folders and parent folders
comments and replies
accounts and parent accounts
organization charts

Whenever one row points to another row in the same table, the Hierarchy Pattern may apply.

A Note on Recursion

A self-join is useful for one level of hierarchy.

For example:

employee → manager

But if you need multiple levels:

employee → manager → director → vice president

then a single self-join may not be enough.

For deeper hierarchies, many databases support recursive queries, often using recursive CTEs.

The exact syntax varies by database system.

But the underlying idea is the same:

A row can relate to another row in the same structure.

When I Reach for This Pattern

I typically use the Hierarchy Pattern when:

a table references itself
one row has a parent row
I need to display a parent name
I need to build a basic hierarchy
I need to connect a child row to its immediate parent

Examples include:

employee-manager reports
category trees
folder structures
comment threads
account relationships

Key Takeaway

Not every relationship crosses tables.

Sometimes the relationship lives inside one table.

The Hierarchy Pattern helps answer:

How does this row relate to another row in the same table?

A self-join lets one table play multiple roles in the same query.