Database

How to Represent and Query Hierarchical Data in SQL

A tutorial on how to build and query a hierarchical table using a relational database.

Photo by benjamin lehman on Unsplash

Given its flat nature, a relational database is not suitable to represent hierarchical data. However, thanks to some tricks, you can transform a relational database into good storage for hierarchical data.

In this article, I cover the following topics:

• definition of hierarchical data
• how to convert hierarchical data into a relational table
• how to query a hierarchical table

1 Definition of Hierarchical Data

Hierarchical Data is a data structure where items are related to each other in a parent/child relationship. The following figure shows an example of hierarchical data:

Image by Author

At the first level, there is a parent class, called Animal, which is the father (or root) of all the other classes. Then, at the second level, there are three children, Mammal, Bird, and Fish, all at the same level. Finally, there is a third…