As a thank you note to all my followers, I am planning to explain and explore "Recursive SQL" which most candidates I have interviewed so far don't have a clear understanding of. So I presume this topic is an ideal candidate to discuss here.
What is Recursive query and when do you typically need to use it?
Recursion is a process of querying iteratively, and to perform a function multiple times on the data of interest. It comes in handy when you need to access hierarchies of data. For example, when you need to access multi-categorical data (with multiple levels), such as Home Furnishings -> Dining -> Tables. OR looking to navigate organizational structure : CEO -> Senior VP -> VP -> Senior Director etc. You get the idea?
Knowing when to make use of Recursion is nearly as important as understanding the processes of recursion itself. So, think about the result you need and list down the options you have to achieve that result. There could be other straight forward approaches to solving a problem than recursion, so choose carefully. Note that for illustration purposes, I have used a simple dataset, whose result may be obtained in different ways. I have used recursion merely to show a working example.
Components of a recursive SQL:
3. Termination and final output
This is the portion of the SQL that bring the subset of data, on which recursion is to be performed.
Operation to be performed multiple times on the seed
Final output after the recursion completes
WITH RECURSIVE (parameters that need to be passed to recursion) - to invoke recursion
UNION ALL - to combine the intermediate results together
Illustrating an example of going through a hierarchy from top to bottom. Home & Garden has 2 subcategories: Dining and Outdoor, who in turn have their own subcategory: table, chair, hammock. We want to navigate through each of this category from top to the lowest available level.
Working SQL emulating above: