Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive ctes
26. Graphs in computer science


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:


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

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.


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.