This is the first in a series of posts about Oracle SQL and hierarchical data.
Hierarchical data is any data which can be represented in a tree-like structure. Some everyday examples are an org chart or a directory structure (aka filesystem tree).
Two key things characterize the tree structure of hierarchical data.
First, in a finite data set, there’s a root node: one that doesn’t have any parent. In a filesystem tree, it’s the root directory. In an org chart, it’s the ceo.
Second, each child node only has one parent, but a parent node can have many children. A file can’t be under two folders, and an employee can’t be under two bosses (Well, at least not officially).
In a relational database, we can represent tree data in a table with this simple model:
All the data goes in one table.
The parent ID is a foreign key referring back to the primary key on the table itself.
The classic example is the EMP table. Each row contains a manager id, which is itself an employee id. The manager ID is a foreign key pointing back to the employee ID.
This meets the two characteristics of hierarchical data:
- The root node, the CEO, doesn’t have a manager ID.
- And, each employee has at most one manager, in the manager_id column.
There are two ways to query hierarchical data in Oracle SQL. The first way is using the CONNECT BY construct, which has been part of Oracle SQL since the earliest versions of Oracle. The second way, which was introduced in 11g, is to use a recursive WITH subquery. In the next post, we’re going to use the CONNECT BY syntax. We’ll look at recursive WITH in subsequent posts.