Recursive SQL Queries Tutorial | Learn to write SQL Queries using Recursion

this video is sponsored by brilliant hey guys tawfiq here in this video let's learn how to write recursive sql queries now recursive sql queries is a very complex concept in sql but having said that it's not something that everyone uses on a day-to-day basis but still there are several problems in sql which you may be able to effectively solve just by using recursive sql queries so definitely something that can be useful in certain cases especially if you are in a senior role or if you are applying for an interview for a senior role then you might expect some questions on recursive sql queries now in this video i am going to explain the concept of record csql queries in the most simplest way possible i am hoping that even if you are a beginner in sql after watching this video you should be able to understand what recursive sql queries are and how to write a recursive sql queries now before i can start i'd like to thank the sponsor of today's video which is brilliant now brilliant is an online learning platform where you can find interactive courses related to math science computer science and so many others now the best part about brilliant is that every single course that you find on brilliant is designed to be interactive and visually appealing so if you are someone who prefers the interactive style of learning where you get to solve multiple different puzzles and answer several different questions related to the concept that you're learning then definitely you should be considering some of the courses on brilliant now the best part about brilliant is that you can start learning on brilliant for free you can just use the link that i will be leaving in the description below to sign up for free and then start learning now the first 200 people who will be using my link will get a 20 off on your annual subscription so definitely check that out and thank you brilliant for sponsoring this video okay so i'm going to start by explaining you the syntax of how to write recursive sql queries once we understand the syntax i'm going to explain you how sql will execute a recursive sql query okay once you understand that then we will try to solve three different sql queries using recursion hopefully with this you will get complete understanding of how to write recursive sql queries now one thing i want to highlight here is that i am using postgresql here but the syntax and the logic of recursion is basically going to be common across any of the other major rdbms like oracle mysql or microsoft sql server in fact at the end of this video whatever query i will execute here right whatever query we will be solving i'll try to execute the same query in oracle database in microsoft sql server database and also in mysql database so that you get an understanding that we can basically use the similar method of recursion in any of the other rdbms of course there is a few minor differences in oracle and microsoft sql server when it comes to the syntax and i'm going to explain that a little later okay but 95 percent the syntax and the logic the logic is 100 same the syntax there is a slight difference in oracle and microsoft sql server uh it's just very minor difference i'm going to explain that later okay first let's start by understanding the syntax now it's mandatory that you need to follow this syntax in order to make your query as a recursive now basically in order to write recursive sql queries we generally do that by using the width clause now this with class concept is exactly the same that we generally use for any other purpose right the only difference is when we mention with clause we need to mention a keyword here called as a recursive okay now this is a mandatory keyword in postgresql so after we mention the recursive keyword then we need to provide a name for our with clause it can be any meaningful name that we can think of okay and then again we have the as clause and then we need to open the parenthesis and inside here we need to mention our query now the query that we are mentioning here there is a slight special condition that we need to follow and that is we need to basically have two separate queries and both of these queries needs to be merged together by using the union or the union all operator okay now the first part of this query or the first query itself here should basically be the non-recursive part of the query or it can be also called as the base query okay so this is basically where you provide your base input based on which the recursion should happen right and then the second query that is written under the union or the union all operator is basically the recursive query that we need to write this is where we need to use the cte table that is whatever table that we have created using this with clause right this cte name right we need to use that in this query and then we need to also provide a termination condition or a join condition based on which whenever that joint condition or that filter condition will fail the recursion will come out that is the recursion recursive sql query execution will stop now let's say if you do not provide a proper termination condition then the recursion execution will basically keep on executing infinitely and that can be a problem right so it's very important that we provide a proper termination condition and it's important that we have a recursive part of the query here and it's important that we have a base query here right so these are the three important things that we we need to remember when we are writing a recursive sql query we need to use with clause we need to have the recursive keyword we need to have a base query then we need to have union or union all operator then we need to have a recursive query which is basically going to use the same table that was defined in our with clause and it needs to have a termination condition and finally all of this will be executed by using our main query that is this one here now i hope you understand the syntax but you will better understand this when we will go into solving uh these three queries that i have mentioned here now before i can solve sql queries using recursion let me quickly explain you how sql will process the recursive sql query statements right so as soon as i execute this query right the first thing that sql will do is it will come to know that this is a recursive sql query so it will look for a base query so it will find the base query here and it will execute this so since it's a recursive sql query what this means is the same query will get executed multiple times until a particular condition is met right so in the very first execution or the very first iteration of the execution this base query will get executed so your output will basically have the result that was returned from this base query so with the very first record or the very uh first initial records will basically be the output that was written from your base query and this is basically the first iteration of your recursion okay so once this is done then the whole thing that was returned from this base query will kind of become the input for your uh recursive query that we will be defining here okay so in the second iteration of the query this query gets executed that is the recursive part of the query and here it's going to use the input that was returned from the first iteration right and then if it will also check for this termination condition and let's say the termination condition is not met then it will again go into the third iteration but when it goes to the third iteration it will take the input that was or basically the output that was returned from the second iteration okay so in the first iteration the base query gets executed in the second iteration the recursive query gets executed but it will take the input or basically the data that was returned from the first iteration that is from the base query but when it goes to the third iteration it will take the output that was returned from the second iteration okay so this is very important for you to understand because based on this logic only the recursion happens and only if you understand this you will basically be able to understand how sql is actually returning the data that it is returning okay now if you have not completely understood this then it's okay when i'm going to solve these queries and when we will look at the real data you will better understand that okay so now let's straight away get into solving these sql queries the first one that we have is this one the query states display number from 1 to 10 without using any inbuilt functions now if you're familiar with any other programming languages like java or python or any other if you have this kind of a requirement very easy way to solve this is just by using any loop statements right mainly like for loop or while loop or some others right just by using a loop statement we can print a sequence of numbers very easily right but how do we do that in sql now if i was allowed to use some functions or some sequences then i can do it pretty easily but let's say you go to an interview and interviewer will ask you this question and he strictly tells you that you need to solve this problem without using any inbuilt function or any sequences then how do you solve this now one of the ways of solving this problem is just by using a recursion and let's try to solve this just by using recursion in this case okay so i'm just going to open a new window here and let me just copy this question and paste it here now in order to solve this i'm going to use recursion and if i'm going to use recursion i need to follow the same syntax that i just explained to you so i'm going to start with with clause and then i need to mention a recursive and then i need to give a name so let's say i'm going to give a name like numbers because i want to print 1 to 10 numbers right and i'm going to have the as clause and then open the parenthesis and close the parenthesis and this is where i need to write my two queries so the first query that i need to write is basically going to be my base query and in this case i know that i need to print numbers from 1 to ten so my very first number or my very first record has to be one right so i can just hard code it to saying like select one and i'm going to give an alias for this column let's say like n okay and this is basically it this will just print one i don't need to use the from clause here because in postgresql from clause is not mandatory i think if i was using oracle then i had to use from dual but i think in microsoft sql server mysql and postgresql we can just use the select clause to hardcode any value just like this okay so this is my base query that is done and the next part is i need to use the union or union all operator in this case i'm just going to use the union operator okay and the next part of the query is basically the recursive part of the query and i told you when we write the recursive part of the query this is where we need to use the with clause table that is the table name that we have given for our with clause here right or i am calling it like a table because because basically this kind of becomes like a temporary table if you are not familiar with with gloss i have made a video about width class where i have explained all of this in detail maybe you can check that out i'll leave the link to that video in the description below okay now i'm going to use this table that is numbers because as i told you this is a recursive part of the query and here it's mandatory that if i want to make this query as recursive i need to use this table okay so i'm going to use numbers now what do i do next so let's say i got this number so what exactly is happening is as soon as i execute this query okay i will also write my outer query here so i'm just going to say select star from numbers okay and let's say when i execute this query first thing what sql will do is it's going to execute the base query and this is going to return one and then when it goes to the second iteration whatever was written from the base query will kind of be the output that was that will be available in this table that is numbers right so these numbers would basically have one column with one record and value as one right and i can capture that just by using this column n because i have given the name n here right and then what i want is in my second iteration i want it to return two so i get one in the first iteration two in the third iteration and then uh sorry in the second iteration and then when i go to the third iteration it should return three and then when i go to the fourth iteration it should return four and so on and so forth right but how do i make this to return two i can just do n plus 1 because i know in the first iteration n was 1 so 1 plus 1 is 2 and this would should return basically 2 right and when it goes to the third iteration n would have been 2 because it takes the output from the previous iteration and in the previous iteration the n was 2 so 2 plus 1 will be 3 and then again it goes to the next iteration n would be 3 so 3 plus 1 is 4 and so on and so forth it continues but the one thing that i also need to add here is a termination condition that is at what stage this query execution should stop because if i just execute this query as it is then this is going to execute infinitely and that is not what i want right so in order to give a stop for this execution i can just put a filter condition telling that where n less than 10 so what happens is as soon as n reaches 10 it's going to finish this execution because this condition will uh become fail right so let me just execute this query and if i execute you can see that i am getting the output that i wanted so you have one column and it's printing 1 to 10 and that is exactly what i wanted i hope this is clear but you might think why did it just print 10 because i have again less than 10 so should it just not have ended at 9 if you are someone who has that question let me explain to you so what happens was so let's say at one stage n becomes 9 right so when n becomes 9 9 plus 1 is 10 so it is going to print 10 but 9 is still less than 10 right so this is still true so it will still print 10 but in the next iteration n will be 10 so 10 plus 1 is 11 and here 10 is less than 10 is actually not true so that is when it comes out so that is why 11 is not getting printed okay now it's going to print 10 here even though i have a condition checking that n is less than 10 is because i am printing n plus 1 ok so i hope you understand that i hope this query is clear and you kind of get some understanding of how to write recursive sql queries okay so if you're clear on this let's go on to solve the second problem that i have and the second problem is stating find the hierarchy of employees under a given manager so let's say you have been given a manager let's say i have a manager like asha okay and your question states that you need to find all the employees who are working under this manager asha so asha might have some employees working under her and these employees may also have some other employees working under her so i want to get a list of all the hierarchy of employees who are working under asha now in order to solve this problem we will of course need some table data some data set right and i have already created that so i'm going to just paste the question here and the table that i have already created is the employee details table so i'm just going to query that table and show you the data so let's say i have this table there are a few different columns i have id the employee name the manager id their salary and designation right and there are 10 employee details here and if i look for the employee asha you can see that her employee id is 7 and she is a cto now cto is also you can treat it like is a person who will be managing multiple people under him right so asha would have some people under her so let's look at that so asha is employee id seven and here i can see that i have two records or basically two employees who basically is managed by asha so michael is an employee who's having id as five and her manager or his manager is seven that is asha right and then this michael is also a manager because you can see that he is actually managing three other employees so michael is managing three employees asha is managing two employees uh so i need to write a query which is basically going to print me the hierarchy of employees so under asha i have michael and arvind and under michael i have satya jiya and david but under david sorry under arvind i do not have any other employees because arvind is actually not a manager i cannot see the employee id 6 under this manager id column right i hope you understand what we are trying to achieve now let's try to solve this problem by using the recursive sql queries okay now again in order to execute this i know that the very first thing that i need to do is use the width clause so i'm just going to say width and maybe i'll just remove this i'm just going to say with recursive and let's say what name should i give so i'll just tell employee hierarchy okay and i'm going to give the as clause and here i'm going to write my query so let's say my first base query is basically going to be from this table itself that is select star from the employee details and here let's say i only want to fetch initially the data only for asha right so i can just tell where name equal to asha so this will basically fetch me just one record that is this one uh where the employee is asha so this is my base query the next thing is i need to use the union operator i can also use union all but for now i'll just use union and then i need to provide my recursive part of the query so here i can just tell select from since i'm going to use the recursive part of the query of course i will need to use this table that is employee hierarchy and let's say i'm going to give an alias for this like let's say just etch okay and then because this edge will just have the details of this particular record that is the base record but when i get this particular record i want to match this record with all the other records present in this table so that i can identify who are the employees under this manager asha right so in order to do that i'm just going to join another table and another table is basically going to be my main table here that is this employee details table and i'm going to give an alias for this like let's say e okay and now i need to provide a join condition which is going to join these two table but this join condition itself is going to also be my termination condition so when the join will fail the recursion should stop right so let's see how do i join this condition based on my requirement my requirement is when i find asha i need to see who are the employees under asha so meaning that i need to take the employee id of asha and then i need to search for this employee id in the manager id column right and then i need to see who are the employees i get there and i need to follow or basically repeat that same step with these employees as well right so let's see how i can do this join so my first record is asha and that is what will be returned in my base query since it is going to be written by base query my employee hierarchy table will initially have this record right this record number seven or the record with employee asha right so i need to take her id so i'm just going to say h dot id okay and then i need to compare it with the manager id from this entire employee details table so i can just tell e dot manager id right and i think that's all and this should basically work now i just need to mention the list of columns here what i'm just going to do is let's say i'm just going to mention all the column names that i want okay so i'm just going to say id okay and then i'm going to say name i need the manager id and then i need let's say i don't need salary but i'll just say i need designation okay and the same thing i'll just copy it below here but i'll give the alias here because i have two different tables which may have the same columns right so i'm going to extract all of these details let's say from my employee table so i'm just going to say e dot and i'll just copy that here basically paste it here and i think that's it it should work and finally i need to write my main query and this is where i'm just going to say select star from my employee hierarchy table right this is my with clause table and i think that's all and now let me execute this and you can see that the query execution worked fine and i'm getting some data now if you look at this data carefully you can see that i have asha that is my first record here and then under asha i knew that she had two employees under her that is michael and arvin and that is what is getting returned in these two records and then i know that arvind does not have any other employees under him but michael has three under three employees under him they are satyajiya and david and that is what is getting displayed but it's not very clear to understand the hierarchy of employees here so for that i'm just going to add another column here let's say a level okay so just to understand on which iteration which records where fetched okay so my first iteration basically is going to be this uh result from the base query and for that i'm just going to call it like let's say level okay lvl and then i'm just going to say yeah i'm going to use the level here as well and here i am just going to say this level is coming from my hierarchy table so h dot lbl plus 1 and this will basically be my lbl now okay so what i am doing is in the first iteration the base query will always have level as one and in the second iteration the the next record that is getting fetched from the next iteration right it is going to have level plus one that is basically going to be two in the next iteration that is the third iteration the level should probably be three and so on and so forth okay so this should kind of help us to understand which records were fetched from which iteration right so if i execute this now you can see that i can see for asha i have level one and then michael and arvind are level two this means that michael and arvind are basically the employees under asha and then finally the three last records are basically from level three or from third iteration these are the employees under michael right so i hope you understood this and you got some understanding of how to write a recursive sql queries now before i move on to solving the third problem let me try to execute this same query in all the other rdbms now i'm using oscar sql here but i also want to show you that this is going to be almost the same in any of the other major rdbms so i'm going to start by okay so this is oracle and let's say i go to mysql okay so i'm using as you can see here i'm using mysql workbench i have just created the same table here as well and and i'm just going to copy the same query that i just used in postgresql okay so i'm just going to execute the exact same query but only difference that i will need to do is so if i execute as it is i think it's going to throw me an error because no database is selected so i in mysql i think when we are referring a table we need to specify the database or the schema so i'm just going to say demo dot for my table employee details so i'm just going to say here as well and i think that's all and now if i execute this you can see that i am getting the output exactly what i got previously so it's again having six records i have asha which is level one then i have michael and arvind level two and then satyajiya and david in level three so you can very clearly see that the same query in postgresql worked in mysql as well okay so now let's move on to microsoft sql server and here i am using the tool azure data studio to uh which is connected to my microsoft sql server database so i'm using mac and since i cannot directly install oracle or microsoft sql server i'm basically using docker i have i'm using docker to basically connect to microsoft sql server and oracle okay now let's try to execute the same query in microsoft sql server so i'll just copy or sorry paste it here and if i try to just execute this right i have already created the same table with the same data now if i execute this query it's going to throw me an error now this is where there are a couple of changes in the syntax that we need to do the very first thing is we cannot use this recursive keyword so i need to remove this recursive keyword because that is not required for microsoft sql server so now if i run this i'm still getting another error and that is because i cannot use union here i need to use union all okay and now if i just run this it's going to return me the data and you can see the data is exactly the same so again i have asha with the record one level one michael and arvind belong to level two and then satya gi and david in level three okay so the output is exactly the same and the query is almost the same just two difference we always need to use union all and we should not be using the recursive keyword now let's go to oracle so in oracle if i just execute this i'm using sql developer tool in order to connect to oracle database and here let's say if i just execute this query there will be a few different changes so the first thing is i cannot use this recursive keyword similar to microsoft sql server this recursive keyword is not supported so i need to remove that and let's say if i execute this query now again it's going to throw me an error because uh i need to okay first of all i need to add this union all but before that the current error that i'm getting is because it's telling i need to provide the column alias list okay so here i need to provide a list of all the columns that is going to be returned from this query so i'm just going to do that i'm just going to say id name manager id and let's say designation and then i have level right so let me execute this and you can see i'm still getting the error because of the union operator here again i cannot use union i need to use union all and now let me execute this and now you can see that it's working absolutely fine so when it comes to microsoft sql server we need to remove recursive and union union make it union all when it comes to oracle we need to do those two things but additionally we also need to provide the list of aliases in our weight clause itself okay so i hope you have now seen the same query i have executed it in oracle i have executed it in a microsoft sql server i have executed it in mysql and also i have shown you already to execute that in postgresql okay so whatever you're learning in this video is going to be applicable for all of the major rdbms right so everything is fine now before i move on to the third question let's say you are given an additional task okay so let's say for the same query along with all of these details i only want to display the columns that i actually want so let's say you are told that you just need to display the employee id the manager so so let's say employee id employee name manager name and maybe level okay just that so i don't need designation i don't need manager id but additionally i need the manager name so how do i do that can i add a manager name here i cannot because employee details does not have manager name right so instead of modifying anything inside this recursive part of the query i can just do one thing i can join this table with the employee details table so let's say i'm going to give an alias for this like let's say h2 and i'm going to join it with the employee details table i'm going to give it alias like e2 and i need to do the join so how do i do the join i know that my h2 table that is this hierarchy table does not have manager name so my manager name should come from this employee details table right so i can just tell e2 dot id is equal to h2 dot manager id so for each of these managers that i have here i am comparing that manager id or matching that manager id with the id from the employee details table and from here i will fetch their employee name so what i'm just going to do is i'm going to say h2 dot let's say id as employee id and then h2 dot name as employee name and then let's say h2 dot okay not h2 maybe i'll say e2 dot name as manager name right and then finally i'll just tell h2 dot level okay as let's say 11. now if i execute this now you can see i'm just getting the data that i wanted employee id their name and so it looks like asha's manager is sripad michael and arvin's manager is asha and then satyaja and david's manager are michael now i think this data is more clear and more easier for you to understand the hierarchy of how employees and their managers are linked right so i hope this is clear now finally let's try to solve the third query that we have which is this one that is find the hierarchy of managers for a given employee so previously what we did was we took one employee or one manager asha and then we saw how many employees were working under her okay now it is basically the other way around we need to find one employee okay and then we need to see who are the managers above him so let's say for example right i'm going to take a employee maybe like let's say david okay so i'm just going to copy this i'm going to open a new window here i'm going to paste it and let me query from the employee details table select star from employee details and if i run this i'm getting the data so let's say i have this record david now i know that david's manager is um this one that is five and five is michael so david's manager is michael but michael's manager is number seven the empire id seven that employee id seven belongs to asha so looks like michael's manager is asha and then asha's manager is employee id one and that one is basically this one that is sri pad so asha's manager is sri pad and sri pad basically has no managers because he is the ceo so looks like david's manager is michael michael's manager is asha and asha's manager is shri so we need to write a query to find the managers basically the hierarchy of managers for david so let's see how we can do that now instead of writing this entire query from scratch what i'm just going to do is i'll just copy this entire query because almost 98 of the things will be the same there's just going to be one difference which i'm going to explain okay so i'm just going to paste that same query that we wrote previously and if i execute this query you can see that okay i'm not going to execute this query because we already saw that previously right now what we need to do or basically what modification we need to do such that this query is going to return me the hierarchy of managers and not the hierarchy of employees under a particular manager right so let's look at the first thing the first thing is i am trying to find the hierarchy of managers for david right so the very first thing i will need to do is i need to modify my base query such that it's going to return me the data for david so instead of asha i'm just going to make it david this is my very first thing and now if i execute this okay i will not execute this because i know it's going to just return one record and that is this one right this particular record now from this record when i do the second iteration of my recursive query what i want to do is it's going to return me david with employee id 4 but manager id5 in this case i'm more interested in the manager id 5 because using this manager id i'm going to search for records entirely in this employee table and in this employee table i am going to match it with the id column such that i will return michael and then i am going to perform the same iteration for this michael record as well so for michael again i'll take his manager id and this manager id i'm going to compare it with the id from the employee table right so the only change that i will need to do is where i'm joining this hierarchy table basically this hierarchy part of the query so here when i run my base query i know that it's going to just return me david write this one record now from this one record i need to fetch the manager id of that hierarchy table right so from this edge i need to take the manager id so here i'm just going to say manager id okay and then whatever i get i need to compare that manager id with the employee details table the column id right so my employee details table is e and here instead of manager id i just need to compare it with id okay i hope you understand what i'm doing is my base query will return me david from this david i'm taking the manager id five okay and that is this will be five and this five i'm trying to match it with the entire employee details table under the column id right so that i can get who is the manager under david right so this when i try to match 5 under this id column it's going to return me this record that is 5. so the second iteration will return me this record for michael right from this second record when i go to the third iteration i need to see the manager id for michael that is seven right so this h at that moment will have seven uh sorry h dot manager id will have 7 and it's going to compare it with the employee details id right so 7 will get compared with this column here and it will return this record that is asha right so the third iteration is going to return me asha and then again the same thing will continue for the fourth iteration until i find all the managers okay when it comes to so so let's say when it comes to asha it's going to fetch the manager id one and this one is going to then compare it with all the ids from the employee details table so it's going to return me this record because employee id 1 belongs to sripad right and from here it's going to again fetch the manager id in this case manager id is null right so h dot manager id will be null and when you compare null with any other employee id you can see there are no employee ids which is having id as null so this condition will fail so after i get shri path this entire query or recursive query execution will stop and i will get the final output right so let me just execute this query and see if it actually works how i explain so if i run this now you can see that it's just returning me three records so i have david and david is basically my input employee or basically my base employee right so david's manager is michael and then for michael michael's manager is asha and then for asha asha's manager is sripak but after sripa there is basically no one because sripa does not have a manager right so this is basically how i have solved this problem i hope you understood this and everything that i have explained in this video is clear so one last thing that i want to tell you is when you're learning this concept or when you're watching this video try to solve these problems on your own in your own database so for that you may need this data set so what i'm going to do is all the data set that i have used in this video i will be placing those data sets in my discord server now i'm going to leave the link to join my discord in the description below it's free you can just join and then you will find all the data sets and all the queries that i have written here on my discord so you can use that then it will be much easier for you to understand the concept and learn this concept okay so i hope you found this video useful if you did and if you have not subscribed to my channel yet then please consider subscribing and also give me a thumbs up on this video and whatever feedback you might have about this video just leave them in your comments below i really love reading all of your comments and thank you so much for all the support and see you soon in the next one bye

Channel: techTFQ

Views: 196,142

Keywords: SQL, sql tutorial, recursive sql queries, sql recursion, recursive sql, Learn to write SQL Queries, sql query writing, techtfq sql, sql techtfq, techtfq, recursive cte, practice sql queries, practice complex sql queries, complex sql queries, learn sql, common table expression, recursive sql query in Oracle, recursive sql query in MySQL, recursive sql query in Microsoft SQL Server, recursive sql query in PostgreSQL

Id: 7hZYh9qXxe4

Length: 33min 52sec (2032 seconds)

Published: Wed Mar 23 2022