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
26. Graphs in computer science
Summary

Instruction

Well done! Since you already know how to tackle hierarchical structures represented in the form of trees, it's time to move on and introduce the second type of real-world situation that we can tackle with recursive CTEs: graphs.

Graphs consists of vertices and edges. In real life, graphs can often correspond to road maps. Take a look:

recursion

In a database, we may store vertices (cities) in one table, and edges (the roads between them cities) in another table:

City
Name
London
Cambridge
...
Road
NameFrom NameTo DistanceKM
London Cambridge 78
...

For educational purposes, we will simplify things a bit. We will only use a single table, City, with city names and GPS coordinates and we'll assume that you can go directly from one city to another.

First, let's take a look at this table.

Exercise

Select all data from the City table.

The table contains the following columns:

  • Name – the name of the city,
  • Lat – the latitude of the city,
  • Lon – the longitude of the city.

The last two columns indicates coordinates. Based on those coordinates, we will calculate the distance between two cities in a straight line and treat it as the distance.