Introduction
Infinite looping
Processing trees with recursive CTEs
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.