Mastering Oracle SQL, 2nd Edition

Chapter 8. Hierarchical Queries

A relational database is based upon sets, with each table representing a set. However, there are some types of information that are not directly amenable to the set data structure. Think, for example, of an organization chart, a bill of material in a manufacturing and assembly plant, or a family tree. These types of information are hierarchical in nature, and most conveniently represented in a tree structure. In this chapter we discuss how to represent such hierarchical information in a relational table. We also discuss in detail various SQL constructs that you can use to extract hierarchical information from a relational table.

Representing Hierarchical Information

Let’s look at an example to understand how we can represent hierarchical information in a relational database. As a basis for the example, we’ll use an organization chart showing how one employee reports to another within a large organization, as shown in Figure 8-1.

Figure 8-1. An organization chart

Figure 8-1 represents a hierarchy of employees. The information regarding an employee, his manager, and the reporting relationship need to be represented in one table, employee, as shown in the Entity Relationship Diagram in Figure 8-2.

Figure 8-2. The reporting relationship

In Figure 8-2, the employee ...