Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

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.