How to store and Query Hierarchical Data in Database using SQL? Example Tutorial

What methods do you use to detect hierarchical data and query it? We're about to get answers to two popular SQL queries. When working with SQL and relational databases, querying hierarchical data is a typical occurrence. It's not that hierarchical data is mysterious or uncommon; on the contrary, it's all around us. So, why is hierarchical data so difficult to work within relational databases? The issues arise mostly when converting hierarchical data to relational database concepts.

The first step in dealing with hierarchical data is to recognize it. I'll begin by defining hierarchical data and providing some instances from everyday life. After that, I'll go through how hierarchical data is often stored in databases.

First of all, let's talk about the data. 

What is Hierarchical data?

Hierarchical data is a type of data in which the data sets are organized in a hierarchical order. When you think about hierarchy, what comes to mind? Probably on different levels: something is higher, lower, or equal to something else. A parent-child connection is also known as a hierarchical relationship in relational databases. This indicates that the kid data has just one parent while the parent data has one or more 'children.'

It is usual to describe hierarchical data as having a tree-like structure. When we look at some frequent examples of hierarchical data, you'll realize why.

Examples of Hierarchical data

Employee hierarchies are a common example of how to describe hierarchical data. Organizational charts like this one are used to depict them:

As you can see, the construction is thin at the top and widens as it descends, similar to a pine tree.

The Marketing Director Thomas Edison is at the top with 3 managers below him. the three managers are, as shown above, Marie Curie, Blaise Pascal, and Isaac Newton. Marie Curie has 2 other managers Will Thomson and Carl Gauss under her. Blaise Pascal has 1 and Isaac newton has 2 managers under them respectively. They each have a few employees under them.

If you guys want, there are infinite examples of data that are represented in a hierarchical way.

Few are family trees, computer folders, etc.

Storing hierarchical data in a database

When you try to store hierarchical data in a database, you frequently run into problems. To do so, you'll need to pack all of the multi-level data into a table, which is a rather flat structure. What is the best way to transform hierarchical data into basic rows?

In most databases, a column that refers to the same table is used to hold hierarchical data. What exactly does that imply? I think it's better if I give you an example. The employment structure appears to be ideal for this!

Let's create a table named employees and insert some data in it.

CREATE TABLE employee
(
  id INT PRIMARY KEY,
  name VARCHAR(101) NOT NULL,
  superior_id INT
);

Now, let's recreate the chart that we saw earlier. let's insert similar data.

INSERT INTO employee VALUES
    (1, 'will thompson', 6),
    (2, 'carl gauss', 6),
    (3, 'james watt', 7),
    (4, 'maxwell clark', 8),
    (5, 'paul dirac', 8),
    (6, 'marie curie', 9),
    (7, 'blais pascal', 9),
    (8, 'Isaac Newton', 9),
    (9, 'Thomas Edison', null);

The table in the database would look something like the below:

In a database, hierarchical data generally contains a column that relates to the same table. This is an excellent example. You'll need to write this query to retrieve the table employee's immediate subordinates.

SELECT sub.id as subordinate_id,
	sub.name as subordinate_name,
    sup.id as superior_id,
    sup.name as superior_name
FROM employee sub JOIN employee sup
ON sub.superior_id = sup.id
ORDER BY superior_id;

The result of the above query would be as shown below : 

The employee table is joined to itself in this query. Please allow me to explain how it works. You must use explicit aliases when connecting a table to itself so that SQL knows which data originates from which table – and you know which data comes from which table. 

One table alias is sub in the query above. This indicates that it is the table containing the data of the subordinates. The other alias is sup, which refers to the table containing the data of the superiors. Despite the fact that this is the same table, we're processing it as if it were two separate tables.

Conclusion

I've taught you how to discover direct superiors/subordinates in the example above. This implies you've mastered the ability to just see one level above or below. While this is extremely beneficial, hierarchies may be extremely complex and include a large number of levels. You'll need to understand how to use recursive queries before searching such data. But, this article would not suffice for it. we will discuss it in upcoming articles.