The Recursive Relationship Query is a technique in which you can construct a recursive query that can find data that it is based on other data. In this example, you can build a recursive query that will find all employees who make more than $25k per year within a specific location. Here is a link to the example with the recursive relationship query.
This is a great example of SQL with some really cool effects. I love the visual look of the two different ways you can build a recursive relationship query.
The Recursive Relationship Query is a great example of SQL with some really cool effects. I love the visual look of the two different ways you can build a recursive relationship query.
The Recursive Relationship Query is a great example of SQL with some really cool effects. I love the visual look of the two different ways you can build a recursive relationship query.
Recursion is a cool way to build complex queries in most programming languages. In SQL, this is achieved by building a query that begins with a “select” statement and then recursively continues to build up the query until the end. The Recursive Relationship Query is just a very simple example of how SQL can be used to build complex recursive relationships in a single query.
In this example we are building a recursive relationship query (also known as a recursive CTE) that looks at the database table and determines how many times a given person has had a relationship with another person.
Our initial Recursive Relationship Query looks at the table of our new user and then says, “Hey, I’ve got some relationships. I wonder how many times this user has had a relationship with this person, so…”. This can start to get complicated if we start to get several levels of relationships with only one person. To make our recursive relationship query a little bit easier to read, we can use the WITH statement to construct a recursive CTE.
The WITH statement allows us to use the table from the WITH statement of the previous query as a template for the recursive CTE. This makes it easier to read and allows us to use functions and the recursive CTE is much quicker because we do not have to retype a large amount of code to perform each recursive CTE operation step.
As you can see from the demo, the recursive CTE is really fast. It took me less than 15 minutes to finish the demo. I think it’s because it’s a little more complicated than we’re used to as well as the WITH statement. It’s also worth noting that the recursive CTE is not a complex CTE. It’s simple enough that it could be just a subquery or a single table.
Recursive CTEs have a lot of uses, but this one really stands out for me because it’s an example of how to make a recursive CTE much faster. The recursive CTE in this demo is just a single table. It contains just the primary keys for the players. So, in the demo, it only needs to read the entire table, and then it’s easy to do the recursive CTEs.
