Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
16. Trees in computer science
Processing graphs with recursive ctes
Summary

Instruction

Great! Now that you understand the basics, we will examine some real-world examples.

Let's say we have a company with a hierarchical structure, i.e. each employee has a superior (except for the boss). An employee has precisely one superior, but one superior may have multiple inferiors. An example structure may look as follows:

recursion

In computer science, we call such a structure a tree, where each node (employee) has a parent node (superior), except for the root (boss), which has no parent node.

How can we store these hierarchical relations in a database? It's quite simple: each employee is represented with a row that has a column, SuperiorId, that contains the ID of their superior.

recursion

Take a look at our table.

Exercise

Select all data from the Employee table.

As you can see, the table has the following columns:

  • Id – the identifier of the employee,
  • FirstName – the first name of the employee,
  • LastName – the last name of the employee,
  • SuperiorId – the identifier of the person above that employee.