Recursive query writing in SQL is a tricky task and can take some time to master. The real challenge lies when this should be used and when it could turn out to be an over kill.
It takes some mastering of basic concepts in SQL before attempting on a recursive query to solve a problem. Once the fundamentals in SQL query writing becomes second nature then comes the advance features, like recursive query to solve a problem. A simple way to think on its usage would be whether the final outcome needs the intermediate outputs of the dataset to be re used as an input to the next set of data
To further elaborate this ,the classic example of high school mathematics problem of finding a factorial can be a good example
Lets say we need to find factorial of 3 i.e. 3!. It would be
3*2*1 = 6
The factorial of 5 would be
5*4*3*2*1 = 120
and 9!
9*8*7*6*5*4*3*2*1=362880
So, for any non negative integer n , n! could be represented as
n!= n*(n-1)*(n-2)…..*1
If we break down the operation, the result of multiplication of the first 2 numbers gets multiplied to the third number and the result further gets multiplied with the 4th one and so and so forth which confirms on the statement above “the intermediate outputs of a dataset needs to be re used as an input to the next set of data” making it a perfect example for using a recursive query
In Postgres, recursive CTE (Common Table Expression) is used for writing the recursive query and below is the query for achieving 7! .
Explanation:
Starting Point:
The CTE cte_factorial starts with n = 1 and factorial = 1.
Recursive Step:
The recursive part of the CTE adds 1 to n and multiplies the current number (n + 1) by the previous factorial value in the previous step
Condition:
The recursion continues until n < 7. When n reaches 7, it stops.
Final Selection:
The outer SELECT statement shows the factorial value where n equals 7, which is the factorial of 7.This can be removed to see the individual values at each step as well.
Some common scenarios where recursive query could be used to query a dataset would be finding
Organization hierarchy from a table containing 2 columns for employee_id and manager_id, concurrent user logins for website , finding the Product and the Categories for an ecommerce website etc
