Hierarchical Data in SQL Part 1: The Adjacency List

One of the most obvious examples of hierarchical data frequently stored in a database is the organisational hierarchy of employees and their managers.

Typically this follows a one-to-many parent-child relationship whereby each employee reports to a single manager above them, and may have zero or more employees below them who they manage.
This tree is likely to have a relatively shallow structure, perhaps no more than 10 levels deep even in an organisation of 100,000 employees.

The simplest method of storing and maintaining a tree of this type is for each record to include a reference to its parent.
You may see this referred to as an “Adjacency List Model”.
It is worth noting that an adjacency list is a type of Relationship Graph, and that our example is a special case in which we want the graph to form a tree structure.

Representing a tree in this way allows us to easily write simple queries to access the nodes immediately adjacent to a given node (above or below them in the hierarchy), but requires us to use recursion if we wish to query data spanning further levels of the tree.

First, lets consider a simple example where a single table which contains employee names, job titles, and a link to their manager.
(Of course in the real world, we would have normalized this data, and the roles / job titles would not be stored as text in the employees table. However, I want to keep the examples as simple as possible, so that the joins in the queries are purely focused on the hierarchical relationships.)

Here is the code to create and populate the table.

Starting with the simple things, if we know the EmployeeId of a manager, we can query for their direct subordinates:

And, if we wish to return the name of both the manager and the employees managed by them, then we can join the employee table to itself:

To show an employee and their manager when we know the Id of the employee:

And finally, we can always run a query without the WHERE clause and return the complete list of who manages who:

So, what more might we want?

Maybe we need to know the chain of responsibility leading from a particular position up to the top of the organisation.

Or, perhaps we would like to report on a subsection of the tree – for example, listing everyone who is directly or indirectly below the Financial Director.

And what if we want to see what this tree structure looks like?

For all of these we can use recursive queries, and I will present a couple of examples in the next post.

This is part of a series of articles focusing on various aspects of working with hierarchical data in SQL. You can find a list of the other posts in the series here.

Leave a Reply