Subquery in SQL | Correlated Subquery + Complete SQL Subqueries Tutorial

this video is sponsored by brilliant hey guys i am tawfiq in this video let's learn about sub queries in sql now i'm going to start this video by explaining what sub queries are we'll try to write a sql query which contains a sub query and we'll see how sql will be processing a query statement which contains a sub query once you have this basic understanding we'll then talk about the different types of sub queries in sql will try to write different types of sql queries using these different types of sub queries i i'm also going to explain you what are the different clauses where you can use a sub query and what are the different sql commands where we can use sub queries hopefully by the end of this video you should be able to get complete understanding of what sub queries are how to use them and where to use them now before i can start i would like to thank the sponsor of today's video which is brilliant now brilliant is an online learning platform where you would find interactive courses related to math science computer science and many others now the best part about brilliant is that every single course that is available on their platform is designed to be interactive and visually appealing so if you are someone who is a visual learner and prefers the interactive way of learning where you get the option to solve different puzzles and answer different questions and solve different questions related to the concepts that you're learning then definitely brilliant is a platform that you should consider now one more best thing about brilliant is that you can start learning on brilliant for free you can just use the link that i will be providing in the description below to sign up for free and then start learning now the first 200 people who will use this 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 let's start by understanding what a sub query is now in the most simplest of terms subquery is simply an sql query which is placed inside another sql query okay now let's try to understand this better with a simple sql query now i'm going to write a simple sql query and then i'm going to also explain you how sql will process this statement which contains a sub query okay now let's consider the employee table for this now as you can see in my employee table i have four different columns so i have the employee id employee name department name and salary now i have 24 different employee details in this table as you can see here okay now let's say you have been given a requirement where you need to find the employees whose salary is more than the average salary earned by all the employees okay so basically you need to find the details of only those employees who are earning more salary than the average salary earned by all the employees in this particular table okay so whenever you have a requirement to write a query it's always better to break the query into a few parts so it kind of becomes easier to solve okay now just by looking at this question i can pretty easily say that there are two parts to this query the first part is that i need to find what is the average salary earned by all the employees because that is what is asked here right so first thing is i need to find the average salary so i'm just going to write it here find the average salary okay and then once you get the average salary you need to then filter out the data from the employee table based on the average salary that you calculated here right so i can just write like filter the employees based on the above result right something like this so if i had to write a query for this and that is if i needed to find the average salary of all the employees i can simply write an sql query using the aggregate function that is average i can say select average of salary from the employee table right and if i just execute this you can see that i'm getting the average salary and here the average salary is stated like let me just print it here so it's 5791 right so this is the average salary so i have basically got the average salary the next thing that i need to do is i need to fetch only those employees whose salary is greater than this particular salary or this particular value right so how can i do that so in order to do that i can just say select star from employee because i know all my employee details are stored in this employee table and here i just need to add a filter so i add a filter using the where condition and i'm just going to say where salary is greater than because we are looking for employees whose salary is more than the average salary and average salary in this case is this particular value so i'm just going to copy and paste it here okay now if i just run this you can see that i am getting around 12 different employees and this is the expected output okay so you can see that all the employees that i have got here are having salaries greater than 5791 now this query is returning the correct output but the query itself is not actually how we are supposed to be writing sql queries okay and the simple reason for that is i'm hard coding the average value here which is actually not correct because in future if more employees are added to this table then this value will change but our query would still return the same output right so that is why we need to make our query more dynamic and how i can do that is just replacing this average salary value with the sql query that i used here because this is the sql query that actually give me the average salary so instead of printing just the value that i got from this query and then using two different queries to find my output i can just merge them all together into one single query so let's try to do that so i'm just going to replace this average salary here with the query that i just wrote at the top so let's just try to understand this query so what i'm basically doing is i'm fetching all the employee details and i'm just putting one filter condition saying that only fetch those employees whose salary is greater than whatever will be returned from this query now this query that is this inner query will basically return me the average salary from the employee table right so if i just execute this now you can see that i'm still getting the same output but this is a much better way of writing a query because i'm not hardcoding the value in future any changes that happens to this employee table this query itself will always fetch the correct output now the query that we have written here this basically is called as an sub query so the query that you see here that is inside the parenthesis that i have mentioned here this is actually called as a sub query so this is either called a sub query or it can also be termed as an inner query and the outer query that is the whole query here is basically termed as an outer query or it can also be called as a main query okay so this is basically the most simplest example of how you can use a sub query in your select statement now this query is pretty easy to understand right so we have an inner query which is basically a standalone sql query on its own because i can execute this inner query on its own it has no dependency on any other query right and then the outer query here is basically using the inner query to filter the records and fetch the final result right so whenever sql will come across a statement like this how sql will be processing the statement is first sql will look at the statement it will realize that there is a sub query that has been used and it will also realize that it can execute this sub query on its own so it does not need to have any dependency on the outer query so it will straight away execute this query and whatever is returned from this query sql will hold it and then it will execute the outer query and in the outer query it will filter this salary information based on the result that is fetched from this sub query okay so to sum up the sub query itself will only be executed once and whatever output has been written by the sub query will be used to process the main query or the outer query okay i hope this is clear and this is basically what a sub query is and how sql will be processing a sub query okay now this is pretty simple but there are a few different types of sub queries and we can also use sub queries in several different ways and that can get a little tricky so we'll try to explain all of that in this video now with this basic understanding let's look at some other type of sub queries so basically in sql we have three types of sub queries so we have scalar sub query we have multiple row sub query and we have correlated sub queries okay let's look at each of these sub queries one by one let's start by looking at scalar sub query now scalar sub query is simply a sub query which will always return just one row and one column okay now the best example for this is the query that we have just written on the top here now if you look at this query this basically here is the sub query and this sub query will always return just one record and it only has one column as you can see here so it has just one column that is average column and it has just one record and it will always fetch just one record because this is an aggregate function so whenever i am writing an sql query which contains this kind of a sub query this is termed as a scalar sub query and why it is termed as a scalar sub query because it always returns one row and one column okay now this is a good example but then i can also rewrite the same query in several different ways and i'll just give you one other example so it's not mandatory that we can use a scalar sub query only in the weird clause i can also use it in maybe in a select clause or in a in the from clause so let me just give you an example of how we can use it in a from class the same query it will return the same output but i'll just modify this query to use the sub query in the from clause okay so what i'm just going to do is i just remove the sub query from the where clause here and i'm just going to join these two tables so i'm just going to place that entire sub query here and i'm just going to give a name for this so that is an alias for this i'm going to name this like average salvi and here i'll name the column like let's say salary and this one i'll name it like i'll give an alias like e and here because i am joining i will not use the where condition here but i will use the on clause because i am doing an inner join here and in my on clause i am just going to say e dot salary is greater than because that is my condition right i just need to fetch the data for those employees where the employee salary is greater than the average salary so i'm just going to say average underscore cell dot sal right so if i execute this you can see that i'm getting the same output basically i'm getting the same number of records but i'm getting this one additional column okay because since i'm doing a join and i'm using a star it's fetching all the columns from the employee table as well as every single column that is returned from this table here now i'm calling this a table because whenever you're using a sub subquery in your join clause that is in your from clause then sql kind of treats whatever is returned from that sub query as a separate table okay not the actual table that we are aware of but it is kind of sql's way of treating that result set as a table by itself okay so as you can see here i'm trying to solve the same problem using two different queries now it's almost the same query just the difference is that i am using sub query in the where clause here in the top and then in the second query here i am basically using the similar almost the same sub query in the join condition that is in the from clause okay but it's basically returning me the same output if i exactly wanted the same number of columns then i could just say e dot star and even the columns would exactly match with what is written at the upper query okay so these are the examples for a scalar sub query whenever your sub query returns just one column and one row that is a scalar sub query and we can use scalar sub query in many different places i just showed you example for how to use it in a where clause and how to use it in a from clause i am also going to show you examples for you how to use it in several different clauses but a little later in the video okay so i hope you understand this scalar sub query okay i just mentioned scalar e but it's actually scalar sub query now let's move on to the next type of sub query that is pretty widely used and that is multiple row sub query now when it comes to multiple row sub query there are actually two types of multiple row sub query as the name suggest right if a sub query will return multiple rows then it is termed as a multiple row sub query but there are two different types the first type is that let's say multiple column and multiple row okay basically i can just say it like sub query which returns multiple column and multiple row okay so this is the first type and then i have a second type which is basically a sub query which returns only one column and multiple rows okay so both these type of sub queries whenever we write a sub query which will return multiple columns and multiple rows they are also termed under this type of sub query that is multiple rows of query and if you have a sub query which returns just one column but multiple rows even then it is termed as a multiple row sub query we are going to look at the example for both these type of sub queries now now in order to explain this let's try to solve a problem so let's say you have a question that has been asked by your supervisor who is asking you to find the employees who earn the highest salary in each department now there are several different ways to write this query i'll just try to write this query by using the multiple row sub query method so just by looking at this question it looks like i need to first find what is the highest salary in each department so for each department i need to find the highest salary once i am able to get that then i can compare this salary and the department information with the employee table so that i can get the final result okay so let's try to write a query for that first thing what we need to do is i need to find so from my employee table because all my employee information and your salary and department is available in the employee table so i'm just going to use the same table select employee and i'll say let's say i need the department name so i'll say department name and i need the salary right so i need to know what is the highest salary for each department so whenever i have this kind of a requirement where i need to find or do some aggregation on certain column based on some unique values from a particular column it kind of is where i should be using a group by because i need to group the data based on each unique department name and then under each of this group i need to find which is the maximum salary right so in order to solve this i can just use the group by statement i can say group by department name and when i do a group by department name for each department that is each unique value present in the department name column sql will create one group and in that group i want to find who is having the maximum salary so i'm just going to use the aggregate function max and i'm going to get the maximum salary right so if i just execute this you can now see that it's written in just four records because in my employee table i only have four different department information and for each of this department using the group by sql created a group and under that group sql found the max salary and that is what it is returning here so finance has a maximum salary of 6500 admin has 5000 and so on and so forth okay so i basically got the first part of my query now the next thing that i need to do is i need to use this query in order to filter the data from my employee table so let's try to write the second part of the query so i'm just going to say select star from employee and here i need to add it in the filter condition so in this particular case when i'm adding it into the filter condition my filter condition should consider both the columns that is my department name as well as my salvi so in sql we can consider or basically compare more than one column in our filter conditions by placing multiple columns within a parenthesis so i'm just going to say department name comma salary that is a department name and salary from the employee table and i'm just going to say equal to or maybe i'll say in clause here okay because i know that this sub query is actually returning me more than one row so if i used equal to then sql will expect that the right hand side will always return just one row but if i use an in clause then sql will allow even if multiple values are returned from the right hand side right so what i'm going to do next is i'm just going to copy this query and i'm just going to place it here okay so i'll just move this to the right just to make it more neat and i think that's all this one should work so let's try to execute this and then i will try to explain you this query so if i execute this you can now see that it's returning me five different records and if i see here it looks like in the finance department there are two employees darwin and satya who are earning the same salary that is 6500 which was the maximum salary earned in this department and then in hr department corey is earning 8000 which is highest salary then in admin there is monica and diraj in id department right so this query has written me the output so let's try to understand how sql is actually processing this statement okay now i have this sub query here and as soon as sql will execute this query the first thing it will do is it will go and process this sub query so this sub query has no dependency or no relation on the outer query and i'm going to talk about the relation and the dependency in outer query in the next type of sub query but for now when i say no relationship what i mean is this sub query as a whole i can just execute it as a whole i it basically has it's not going to throw me an error and that is exactly what sql will do so sql will first go and execute this sub query and whatever is written from the sub query it will hold that values and then it will try to process the outer query and for every record from the outer query it's going to match this filter condition so it's going to match the combination of department name and salary from the employee table with the values that is written from this sub query that is any one of these four records so if there are any records in the employee table which matches with the combination of these four records which are written from the sub query and then that is what will be returned from this entire query okay and that is what you see here so this is basically what i mean when i say multiple row sub query that is this is a sub query which returned multiple rows but the special thing here is that it's not only written multiple rows but it also had multiple columns so two columns in this case it can have much more than two columns as well so i hope this was clear now let's try to look at the other type of multiple rows sub query and that is let's say the single column and multiple row sub query okay in order to understand this type of sub query let's try to solve another problem so let's say you have been given this kind of a question where you need to find the department who do not have any employees now in my employee table i basically have information about all the department and the employees that belong to this department but then i have another table called as the department table which basically has the information about all the departments and the location that the employees that belong to the department work at and also their id okay now looks like if i use both the department and the employee table i will be able to find out which are the departments which may not be present in the employee table meaning that which are the departments where there are no employees as of now so let me start writing this query i know that i need information about the departments i'm just going to say select start from department and then i need to add some filter condition now my filter condition needs to be something which will eliminate all those departments who are having employees right so how do i find the departments where there are employees so let me write another query here so i'm just going to say let's say it says telex star from employee right if i do a select start from employee i'll get all the details of the employees but i'm also getting all the department names here so basically if i do a select department name from employees these are all the departments where they have some employees right now there are a lot of duplicate records because there can be multiple employees working on the same department so i am just going to use a distinct here so it becomes more clear now if i execute this select distinct of department name from employee you can see that i am getting finance admin id and hr meaning that these are the only four departments in the employee table or basically these are the only four departments where there are some employees right so i'm just going to be using this sub query in my main query here that is here right so what i'm just going to do is i'm just going to cut that and i'm going to have it here so i'll place this into my sub query here now this is fine this will be my whole main query or outer query and this is going to be my some sub query but basically i also need to compare some values right so for that i'm just going to say let's say like this in my department table i have the department name column so i'm just going to say if the department's name is not in this whole list so this whole list will return me only the departments where they have some employees right and if i just do this check where i'm just going to say from the department table find only those departments who are not in this list then i should be able to get the departments which are basically not having any employees so if i execute this you can see that i'm getting only two departments that is marketing and sales these are the two departments who currently do not have any employees so this particular query here is a good example for a single column multi-row sub query and why i'm saying that is the sub query that i'm using in this query here if i just execute it as it as a whole you can just see that it's returning me multiple rows but it's only returning me one column okay and that is exactly what i meant by saying this single column multiple row if your sub query returns multiple column and multiple row it's also called as a multiple row sub query but it kind of will return multiple values so you need to have multiple columns on the left hand side to compare with it and you can also use this multiple row sub queries in the from clause maybe i will try to show it a little later but yes you can use it in multiple different places as well this is just one example and then single row or basically a single column multiple row sub query would be something like this where multiple rows are returned with just one column and you can just compare it with any column in your main query or outer query okay so this is basically all about the second type of sub queries that is multiple row sub queries now let's look at the third type of sub query which is correlated sub query now i think correlated sub query is something that many of you guys have requested me to make a video on so this looks like something that is a slightly tricky to understand because the processing of a correlated sub query by sql is slightly different uh with regards to how sql executes or basically processes a statement which contains either a scalar or multiple dos sub query okay so let's try to understand that now what exactly is a correlated sub query just by the name it's very clear right it's a sub query which is related to the outer query so basically the processing of your sub query depends on the values that are returned from the outer query okay now the two types of sub queries that we saw the scalar sub query and the multiple row sub query i could execute these sub queries by itself i did not need to execute the whole query to execute the sub query because the sub query were not dependent on any other query right that is how scalar sub query and multiple dos sub queries work so when sql processes a statement which contains a scalar or a multiple row sub query sql will just execute the sub query first and it will execute the sub query just once right because once the sql will execute the sub query the data is available or the output is available for sql to use it to process every record from the outer query but when it comes to a correlated sub query it's going to be slightly different okay so let's try to understand this better by trying to solve a particular question so before that i'm just going to write it here a sub query which is related to the outer query okay so this is the most simplest definition that i can give for a correlated sub query now let's try to solve this particular question so let's say you have been asked to write a query to find the employees in each department who earn more than the average salary in that department okay now this question is slightly different from the question that we solved in the beginning in the first question i think what we saw in this video was that we need to find the employees whose salary was greater than the average salary earned by all the employees and we easily could do that by using the scalar sub query but the question that we have now is that we need to find the employees in each department whose salary is greater than the average salary in that department so we need to find the average salary specific to the department that employee belongs to and then compare his salary with that average salary of that department okay so whenever you have this kind of a requirement we can use a correlated sub query of course one thing i would like to say is that every single sql query can be solved in so many different ways so it does not mean one is right and one is wrong in this particular case i'm just trying to explain you how a correlated sub query works okay so let's try to solve this sub query so i'm just going to almost follow the same approach that we did in the beginning so first thing is we need to find the average salary right so in order to find the average salary i'm just going to say average of salary from the employee table and that should be fine but there's one additional check that i need to do here and that is i need to find the average salary with respect to each department so whenever i have with this kind of check or a condition it means that i need to have some filter condition to check for each specific department right so i can just say department name equal to i need to do something right i need to find for uh each specific department okay or let let's say specific uh specific um department something like this okay let's just hold on uh this one will try to find it a little later okay now of course you someone might think that we can also solve this by using group by of course we can and we can solve it in maybe 10 different ways as well but for now let's try to solve it using a correlated sub query okay so my question here states that i need to find only those employees whose salary is greater than the average salary with respect to their department right so let's try to write the query that is the outer query so i'm just going to say select start from employee right and here i just need to put my filter condition i'm going to say where salary is greater than because i need to find only those employees whose salary is greater than the average salary in that department now how do i find average salary in that department so i'll just copy this whole sub query here and i'm just going to place it here so i'll just make it more neater just by pushing it here and i'll move the where clause here and yeah okay so what basically i'm doing is i'm fetching all the employee details and i'm putting a filter condition where salary is greater than the average salary now if i did not have this where condition that is if i just comment out this thing and if i executed this this will still work fine and it will return me all the records but this would be the query which would basically give me the output for only those employees whose salary is greater than the average salary of all the employees because you can see here i am finding the average salary of all the employees from that table okay and based on that average salary of the entire employees or entire company this filter condition will work but this is not the requirement that we have now we need to find the average salary specific to the department that the current employee that i'm trying to process in this query right so for that i can put a condition like this where i just say department name equal to so i'm going to give an alias here i'm saying going to say like this is my e2 and let's say this will be my e1 okay so i'm just going to say e2 dot and here i'll say e1 dot department name okay so before i execute this let's try to understand what i am trying to do here this employee table has 24 employee details right or 24 records for every record what it will happen is this sub query will get executed okay so in this particular case when sql will try to process this statement sql will come and see that there is a sub code it will try to execute the sub query but then it will not be able to execute the sub query because if i execute this you can see it's going to throw me an error because i'm referring to a column in this sub query which is coming from my outer query okay and this is why it is called as a correlated sub query because this sub query here is related or basically dependent on some of the values from the outer query okay so sql will not be able to process this sub query in the beginning because this sub query is dependent on every single value that will be written from the outer query so how sql is going to process this particular statement is it's going to execute the outer query and for every single record that is processed from the outer query it will execute the inner query so meaning that since this outer query in this particular case will be returning me 24 records or basically has 24 records this sub query in this case will get executed 24 different times okay so every one time for every single record from the employee table okay and for every single record from the employee table if i see here right it will first execute this query for this statement that is the employee 1 0 0 1 department name that is admin so this admin will get replaced here because e1 is my outer query table so e1 dot department name in the first record would be admin so it will check department name equal to admin from the employee table so this is like another copy of the employee table that i am using here and if it finds a record in this case it will find the record for the employee department it's going to find the average salary so it is not going to find average salary for the entire employee table it's going to find the average salary only for the employees who belong to the admin department okay so once it finds the average salary from the admin department it will try to do this comparison with the salary from this first record and if there is a success or basically if the salary from this employee is greater than the average salary that is written from here then this record will get returned as the final output okay so if i just execute this query here you can now see that i am getting 12 records and these are the only employees whose salary is greater than the average salary with respect to their department now if you're not very clear about this hold on i'll try to explain this further and i'm also going to show you another query which should hopefully explain you what correlated sub queries are now just to explain you again how sql is going to process this query is or basically this statement is for every single employee sql will process this sub query once okay so and when i say once it's basically going to fetch this department name from the outer query so for every department name from the outer query this sub query will get executed and so in this particular case i can see that i have admin i think there are four different employees belonging to the admin department in this table so there will be four times when this department name that is even department name would be admin and basically it would kind of calculate average salary you would say that it's basically doing the same thing again and that is a problem with correlated sub query because when you have a correlated sub query and if your outer query is going to process let's say millions of records then your sub query is going to get processed millions of times and that is the reason why many times we prefer not to use a correlated sub query so if we can solve a problem by using joins or just by using a scalar or a multi-row sub query we will prefer to use that rather than using correlated sub query but there are cases where you cannot avoid using a correlated sub query and correlated sub queries are very very useful there are cases where using a correlated sub query you can solve the most complex problems pretty easily okay so even though it might process your sub query multiple different times it will still be a very useful in many different cases okay so in the most simplest of terms for every record from the outer query the sub query gets executed that is this is the correlated sub query and the correlated sub query execution depends on the value that is returned from the outer query and in this case that value is coming from this department name column okay so this is basically how i have solved this question okay now let me use another example to help you understand what a correlated sub query is so let's say we have a question like this this basically the question that we solved using i think multi-row sub query just a few minutes back but let's try to solve this question using a correlated sub query okay because this is the kind of question that we generally use correlated sub query very frequently okay so let's say you have a requirement where you are asked to find the departments who do not have any employees okay so how do i solve this so i need to find the departments i know that so i can just say select star from department because my final output needs to be some details of the department so i'm just going to say select start from department but i need to filter out those departments who are having employees right so what i can do is i can use a operator called as not exist okay and here i can write a sub query okay in this case i'm going to use a correlated sub query so i'm just going to say select star from let's say the employee table okay i'm just going to say where and let's say i'll say this alias as e and this alias as d and i'll say e dot department name equal to d dot department name okay now what exactly i'm doing here is i'm fetching all the details from the department table but i am eliminating some records by using this not exist what does not exist will do is if from this sub query if this will return no records for every uh single record that is processed from this statement or from this outer query where this sub query will not written any record then this statement is not x not exist will hold true and that particular record will be returned as part of my final output in other words if i just see what is the data in the department table and let's say it's going to start processing from the first record and in my first record you can see that i have the department name as admin so i have this d dot department name will be admin so this will be admin so let's say in my select in my employee table if i just copy this whole thing here just to give you an example okay and let's say if i replace this with admin and if i run this you can see that it's returning me some records okay now here i have put one it does not matter what you put i can put a column name here or any constant value whatever does not matter just that it should return some column okay whenever i'm using exist and not exist now what happens when i am processing a record from the department table when the department is admin this particular sub query will return me some records right since this sub query will return me some records this not exist will become false so whatever record is being processed it will not be considered for final output but let's say similarly after processing second third fourth it will try to process this record where the department name is marketing right so when i write this marketing and if i execute this you can see that it's returning me no records so when this sub query will return no records this not exist clause will hold true that means it becomes true and whatever record it was processing in this case will be returned as part of my final output okay so if i execute this whole query here you can see that it's only returning me the records marketing and sales because these are the only two departments which are not present in the employee table so only for these two departments this particular sub query here will not return any records and that is why not exist will become true and that is why those two records will be returned as part of my final output so this is also an example for using correlated sub query and this is kind of the place where we use correlated sub queries a lot whenever we need to check if a record exists in one table or the other table or if the record is not exist or something like that right when we need to compare the data between two different queries or two different tables we kind of use this correlated sub queries because for every record from the outer query we can check for corresponding record in the sub query and if the sub query does not return any record then it means that that record is not present in the table we are using in the sub query okay so this is basically what uh correlated sub query is i hope you get some understanding the biggest difference between correlator sub query and all other type of sub queries are that all the other type of subscribers sql will straight away execute the sub query it will keep or hold that output and then use that output from the sub query to process the outer query but when it comes to correlated sub query sql will not be able to execute the sub query as a whole so for every single record that is processed from the outer query the sub query that is a correlated sub query will be executed so depending on how many records the output query will process that many number of times the correlated sub query will get executed because the correlated sub query will have some join condition based on some of the column that is used from the outer query so you can kind of say that correlated sub query would be a little slow because it will get processed multiple different times based on the number of records processed in the outer query but there are many many scenarios where we have to use correlated sub query to solve our problems okay so i hope this is clear now let's move on to the next one now basically these are all the types of sub queries that we have in sql scalar multiple sub queries and correlated sub queries but then we can also have sql queries where we have a sub query inside a sub query inside a sub query kind of like a nested sub query but in general uh nested sub query is also a sub query so we can also call a sub query as a nested sub query but i just wanted to show you an example of an sql query where we would have like a nested sub queries or like a sub query inside a sub query instead of sub query okay so let's try to understand that and to understand this let's try to solve this particular question okay so for this question i will need to use another table and another table that i have is the sales table so if i check this table you can see that this table has i think around 11 different records it's basically information about some store so i have the store id i have the store name and let's say these are all the apple store okay and then i have the product name so these are all the apple products like their phone their laptop etc then the quantity number of units that have been sold and the price so this is basically my sales table now we are going to use this sales table to solve this particular problem okay now the problem that let's say you have been given is to find the stores whose sales were better than the average sales across all the stores okay so let's try to understand this we need to find the stores whose sales are better than the average sales across all the stores now in order to solve this problem let's try to break this into a few parts so what is the first thing that we need to find we need to find the total sales for each store this is my first thing right once i get the total sales for each store i can then find the let's say average sales for all the stores right and once i have this average stores i can then just compare one and two to get my final output right now the first thing here that is to find the total sales for each store why do i need to do this is because you can see in this table for the same store i have multiple records here so you can see for apple store one you can see i have i have three different records here and if i just count the price that is 16500 that is 7500 this would be the total sales that this apple store one has done and similarly for other stores right so i cannot just sum up all the data from this table the first thing is i need to segregate the data based on each store and then i need to find the total sale value and then over that i need to find the average sales for all the stores okay there are a few things that we need to do here so let's try to do the first step here how do i do the first step so i'm just going to say select from sales and here in order to find the total sales that is the total price for each store i can just group the data together so i'm just going to say group by i'll say store name and i'm just going to use the same store name in my select clause here and then i'm going to do a sum of price right and this one i'm going to name it like let's say total sales right so if i just execute this you can now see that i'm getting one record for each store and it's returning me the total sales that has been done by each of this store so i think i have got the first part of this query now the second part of this query i need to do is i need to find the average sales for all the stores how do i find the average day sales for all the stores i can do that by summing up all of this amount and then dividing by number of stores right but i need to do this average on this particular data not on all the data from the sales table because there i have multiple records for the same store and that would not be correct i need to sum the total data that i have got from this query so basically uh on the output that is written from this query i need to find my average right so what i will do is i'll just use this query itself so i'm just going to save select from and here i'll just move this right and let's say i'm just going to use this as a sub query here okay so i'll just move this to the right i'll close this and here i'll just say average of okay this particular column that is total sales right and okay so i think okay and i'll give an alias here and i think this should pretty well work and yes i'm getting the average sales right so i have got the average sales for all the stores by using both of this query and now my third part of this query is to compare one and two now my one is this and my two is the combination of these two so in order to do this comparison i'll basically need to reuse this one more time right so what i'm just going to do is i'm going to write a query so let's say select from and my first part of this query was this one where i found the total sales for each store and i'm going to place this inside the parenthesis as a sub query i'm going to give a name for this like let's say sales and then i'm going to do a join and i'll join it with this whole query here so i'm just going to move this to the right and let's say i'm going to name this whole thing like um average sales okay and here i'll give an alias for this like let's say sales okay and then finally i am going to join these two table by using the on clause and my on clause is going to join based on sales dot total sales so my requirement here is i need to find whose sales were better than the average sales so if you if it should be better than the average sales then i need to put a greater than symbol here and then i'm going to say average underscore sales dot sales right so and here let's say i'm just going to do a star and now if i execute this query okay i'm getting an error i think yeah i missed a parenthesis here so let's add a parenthesis okay i think should be fine now so let's run okay so now i'm getting the output and you can see that there are only two stores that is the apple store three and apple store one whose total sales are greater than the average sales so i have nine thousand seven hundred and seven thousand five hundred and average sales are six thousand fifty so if i just execute this inner query here just to see what were the total sales for from all the stores you can see that i have 9700 so this apple store 2 only had 2 000 of sales and apple store 4 had 5000 whereas average sales that we got was around 650 right so only two records actually satisfied our condition and that is why this whole query is returning only two records now this is fine and the one thing that you need to focus here is i have this outer query inside that i have this sub query and then i'm joining it with another sub query but this another sub query this second sub query here again has another sub query used here so i have a sub query inside another sub query inside another sub query okay so this is just an example that i wanted to show you how you can write sql queries where you have multiple different sub queries nested one after the other okay now there's one last thing that i want to tell you about this query and that is this is actually not the best way of writing this query and the simple reason for that is if you look at this sub query here this sub query is basically repeated here and the same thing is again repeated here so always remember whenever you're writing a sql statement and if you're using a sub query and if you're using the same sub query multiple times in your query then it's a perfect occasion for you to use with clause okay so let me try to modify this query by using the width clause i am just going to say with and what i'll do is i'll just copy this whole thing here okay this whole the sales sub queries i'm just going to name it like with sales as and i'm going to place it here okay and then i'll write my main query here and i'm going to say from and basically going to copy the same from clause okay so i'm just going to copy the same from clause but i'll do some small changes so i'll just remove this okay first change that i'll do is since i'm using this sub query as inside my with clause and i'm calling it like sales and just going to replace this sub query everywhere in my query with that name that is sales okay so similarly i'm using the same sub query here so i'm just going to call it like sales and i think that's all so what i have done is the sub query that i was using multiple times so i was using it here and i was using it here what i did was i just removed that from there and i put it inside the width clause i gave a name for that like sales and everywhere wherever i wanted to access that sub query i'll just use this name that have given in my width class okay that is here sales and here as sales and now if i execute this the output should still be the same okay it's not showing output because i did not put anything in the select clause so i put a star here now and now if i run this now you can see it's returning me the basically the same output that was returned from the above sub query so this is just an example of how you can use nested sub queries and how you can replace multiple different sub queries or basically the same query which is used multiple times in your query by using a bit clause this is a much better way of writing an sql statement okay i hope all of this was clear you have understood all of these concepts now i just want to explain you what are the different clauses in sql where you can use a sub query now to answer you straight away there are four clauses in sql where you can use a sub query so the first one is we can use a sub query in select right and we can use a sub query in from and we can use a sub query in the where clause and we can also use a sub query in the having clause so i think i have already shown you queries where we have used the sub query in the from clause and the where clause so you can see that just this previous query here i have used this sub query in my from clause so this is definitely example for using a sub query in your form clause now when it comes to the where clause i think we have shown you some example so you can see that this is one example of where we can use a sub query in the where clause right but i think i have not shown you example of how to use a sub query in the select clause and in the having clause so let's try to understand that okay so first let's see uh using a sub query in select clause okay now this is actually not recommended it's always better that you do not use a sub query in the select clause but i still want to show you how you can do that and how i'll also give you an alternative way of how to achieve the same result without using the subquery in a select clause okay so let's try to understand this with another problem so let's say you have been given this question where you have been asked to fetch all the employee details and add remarks to only those employees who earn more than the average pay so basically you need to fetch all the employee details so you are basically not asked to filter any records but for every single employee that you will be fetching you need to display some remarks column and in the remarks column you need to mention if that employee is earning more than the average salary for all the employees okay so let's see how we can write this query now i know that i don't need to do any filter so i can just fetch all the details from the employee tables i'm just going to say select start from employee and this will fetch me all the 24 employees from this table now this is fine the additional thing that i need to do is i need to have a remarks column okay now i'm just going to add a parenthesis and i'm going to call this like remark so this is my column where i need to add a remark saying that this employee is earning more than the average pay and how do i do that so i can just write an sql query here itself so let's see how i can do that i'm just going to say okay but because i need to display this remark only for those employees who are earning more than the average pay and not for all the employees whenever i have this kind of a condition conditional statement what i can do is i can add a case statement so what i'm going to do is i'm going to say case when and let's say i'm going to say salary okay this is from the employee table is greater than and here i'll pass my sub query so my sub query would be something like select average of salary from let's say again the employee table because my salary is present in the employee table and i think that's all and yeah so i'm basically comparing the salary for every record from the employee table with the average salary that i will get from this sub query here okay so this is basically a scalar sub query now this is a condition when you are using a subquery in a select clause it has to return just one record and one column if it if it returns more than one column or if it returns more than one row then it is going to throw an error okay so here i am just going to say if salary is greater than the average salary then i'm going to print like higher than average okay and else let's say if it is any other employees then i'm just going to print like let's say null okay and i'm going to do an end case here and i think that should be good enough okay so if i execute this query you can see that i'm still getting 24 records but there's a new column added here at the at the end and this column you can see that it's null for any employees whose salary is less than average uh salary but for any employees who's earning more than the average salary you can see it's printing like higher than average okay so it's printing higher than average etc okay now this is basically an example i wanted to show you of how you can use a select clause in your sql query basically how you can use a sub query inside your select class now trust me whenever you have this kind of a requirement you can always find alternative ways to remove the select clause or basically to remove the sub query from your select clause this is not the best method of writing a query simply because if you write a select query inside your select clause then for every single record your query will process this whole sql query that you have mentioned in your select class will also get processed and you can easily replace this sub query by putting it inside your join condition okay so always try to avoid using a select query inside your select class or a sub query inside your select loss because you can easily put that inside your join condition okay so let's say for this if i had to basically remodify this query such that i remove the select clause from here so let's say i remove the select clause from here and put it inside the join condition so i'm just going to say i'll move it here i'll say join with this whole sub query i'm going to name this like let's say average salary and here this column as let's say salary okay and since i do not have any join conditions here and i need to fetch all the records from the employee table i'm just going to do a cross join here okay and here i'll say average underscore salary dot sal and so basically what i'm doing is i use that sub query in my from clause i give an alias for that and then i'm just using that alias and that column from that sub query right and that's all now if i execute this you can see that i'm still getting 24 records and i'm still getting basically the exact same output that i got from the above query okay so i have just replaced the same query here that you can see where i was using the sub query in the select clause by just using that exact subquery in my form clause okay so this is a much better way of writing a query rather than using a select query or a sub query in your select class okay but i still wanted to show you how you can do this okay hopefully this is clear now let's see how we can write a select query using in the having clause okay and this can actually be pretty useful in certain cases so let's try to understand this by solving a problem so let's say you have been given this question to solve and you have been asked to find the stores who have sold more units than the average units sold by all the stores so basically we need to find the stores who have sold more units than the average units sold by all the stores so let's try to write this query by using a sub query in our having clause okay now i know that my stores information is present in my table sales right so i'm just going to say select start from sales and if i execute this you can see i'm getting all of this data i need to find for each store how many units they have sold so whenever i have i have to do this kind of aggregation based on each specific store i can just use a group by so i'm just going to say store name comma and i know that the column that i need to use here is quantity because this is basically the column which is going to tell me the number of units that have been sold and here i'm going to use a group by so i'm just going to say group by the store name and i need to group the data based on store name and i need to find the total quantity or total number of item that they have sold and you can see i have multiple records here for the same store and i need to sum these quantities together to identify how many units have been sold totally by a particular store so for that i can use the aggregate function as sum and i think this should be pretty okay if i execute this you can see that i'm getting the store 3 apple store 3 has sold 11 units store two has sold two units and so on and so forth okay but this is basically all the units that have been sold by each store so this is just one part of the query the next thing that we need to do is we need to only fetch those records or basically those stores who have sold more units than the average uh units sold by all the stores so how do i find the average units sold by all the stores i can just find every unit sold by all the stores just by using the every average aggregate function so i'm just going to say average quantity of this one from sales right if i execute this you can see i'm getting it like two so i can just use this sub query somewhere in my outer query so let's see where i can use that since i'm using a group by already in my query here i can use a having clause here and in my having clause i can basically compare this value because this is the total quantity or items that have been sold by each store right so i'm just going to copy that same thing here i'm going to say quantity and i'm going to compare this okay with the sub query that we just wrote so again if i just have to explain this is basically giving me the average units that have been sold by each store and my group by query here was returning me the total items that have been sold by each store okay and then this total items that i have that i got from the sum of quantity i'm just checking if it is greater than the average units that have been sold then only fetch those records so now if i run this currently there are four records and if i run this entire query you can see there is only three because apple store three apple store one and apple store four are the only stores who have sold more than two units in their store okay and that too is basically the average unit sold by each store right so this is basically how i have solved this query and you can see that i have used a sub query here inside my having clause okay so i hope this was clear how you can use a subquery in your having clause as well now let's move on to the last part of this video i know that this video is pretty long and i really didn't want to make it very long but there were so many different things that i wanted to explain about sub queries that i had to be long okay now the last part that i want to cover in this video is going to be all the different sql commands where we can use sub queries now we have seen sub queries in the select queries uh basically in the select statement we have seen all of these examples where we used sub queries in the select statement but we can also use sub queries in some other sql commands and they are we can use it in insert okay and we can use it in update and i can also use it in delete okay so other than select so if i just say sql or sql query right so basically these are the four statements where we can use a sub query right so we have already seen all of this so this is done and now let's see how we can use a sub query in these three statements okay so let's start by looking at insert okay so let's say you have a requirement where you need to insert data to an history table that is employee history table okay so all the employee information you need to store it in the employee history table and we should make sure that we do not insert any duplicate record so once the employee information has been stored in the employee history table once again we should not be storing the same employee information in the history table okay so let us say you have this kind of a requirement where we always have some history table in our project site and how do we basically populate the history table okay so let's see how we can do that now for this i already have created a table and that is the employee history tables i'm just going to show you that employee history and currently this table is blank there is no data in this table as of now now my requirement is that i need to store the employee information in this history table right so this means that the information is already present in some other table so i think this table requires the employee id name department name salary and location looks like these information are available in the employee and the department table so i just need to refer to the employee and the department table and fetch the data from those two tables and then load it into this history table right i don't need to write insert script for each and every record because this data is already available in some other tables already in our database so whenever you have this kind of requirement where you need to populate a third table or like let's say some other table but based on the data available from some existing tables then this is where we will need to use some sub query or basically an sql query to load data into this new table okay so let's see how we can do this so i can just write my insert command insert into this table name that is the employee history right and then here i can just write my select query okay so i'm just going to say select from the employee table because i know most of the details are coming from the employee table i need to join it with the department table because i need to fetch the location as you can see here i have the location column that i need i'm just going to give an alias for department like d and i'm going to join it based on d dot department name equal to e dot department name and okay i think this is fine now let me put all the different columns uh i'm going to specify e dot employee id comma e dot emp name comma d dot department name comma e dot salary comma d dot location i think these are all the columns that we need and these are the exact columns that are present in the employee history table so that should be fine now there is one check that i need to do here and that is i should only insert an employee detail just once in the history table so if the employee information is already present in the history table then i should not be inserting it again okay now in order to add this kind of a check i can add a where condition here and here i can just use my not exist clause and here i'll just put a query here saying that i'll write a sub query here that is the correlated sub query and i'm just going to say select one from let's say employee history table okay and i'm going to call this like eh and i'm going to say e h dot emp id equal to e dot emp id okay now if i had to explain this query what exactly i'm doing is now i think this is pretty self explanatory where i'm just joining the employee table with department table i am fetching all the columns that i need for my history table and i am joining these two tables using the department name now that is fine but the problem is if i did not have this where condition then if i executed this query multiple times then the same employee details would get inserted into the history table let's assume that the history table does not have any primary key or any constraints okay so duplicate data also might get inserted so how do we eliminate that and that one i can do that by this where condition what this where condition is doing is it's checking or basically it's only going to insert those records which are not existing in the employee history table okay so this not exist will check if this this data here or this sub query here is going to return any value so this sub query here i'm basically querying from the employee history table for every employee id that i'm getting from this query here this outer query here i'm checking for that employee id in the employee history table and if the employee already present in the employee history table then this record is going this software is going to return me some records right and as soon as it returns some records this not exist will fail and hence this whole query would not written any record okay but let's say this my outer query will return an employee which is not present in the employee history table then this query here would not return any record because employee history does not have that employee so since this is this sub query is not returning any record this not exist would be true hence this whole query would return some data and that is what would get inserted into the employee history okay so that is basically what i am doing now let me try to execute this and you can see that it's inserted 24 records okay and now let's say if i execute this again i'll execute this again okay and now you can see it's inserting zero records why those 24 have already been inserted into the employee history and because of this where condition it's not going to fetch those 24 records again okay so this is basically how we can see in the data in employee history now we can see there are 24 records and it has all the location information as well okay so this basically how we can use a sub query to load data into a table using the insert command okay or basically how we can use a sub query in the insert command now let's move on to the next one and that is the update command so i'm just going to say update and let's understand this update again by looking at a question let's try to solve that so let's say you have been given a requirement to increment or basically you need to give 10 percent increment to all the employees in the bank location so only in the bangalore location based on the maximum salary earned by an employee in each department so what is the increment that you need to give you need to find the maximum salary in each department and then give a 10 hike on that maximum salary okay and then you need to give this increment only to those employees who are working in the bangalore location okay and then you need to only consider employees in the history table who are available in the employee history table okay so if there are any employees who are available in the employee table but not available in the employee history table then those should be ignored okay so there are a few checks we need to do now let's try to do this by an update statement so we know that when we need to update a table we first need to say update that table name so i'm just going to say update table name e and then i need to use my set command and then i need to use my where command okay now inside my set command so let's try to write this okay i know that i need to update the salary so i'm just going to say salary equal to and here i need to add a sub query okay why i need a sub carry here is because the value with which i want to update this column is dependent on a few things so i need to find out the maximum salary for each department and then over that i need to give a 10 hike right so let's try to write that i'm going to say select okay and here i'm going to say select from the employee table because in order to find the maximum salary earned by each employee in the department i again need to use the employee table and instead of using the employee table i'll use the employee history table because i know that i only need to update data or employees who are present in the employee history table so i'm just going to use the employee history table itself and i'm going to give an alias for this like let's say e h and how i'm going to join this is i'm just going to say e h dot department name equal to e dot department name so kind of i'm creating a correlated sub query here so this sub query will be dependent on the outer query that is this employee table now you might think that this is not an outer query so it's not like we call just the select statement as an outer query even this update statement is basically an outer query okay for sql so this will kind of become my sub query so for every department it's going to find the maximum salary so i'm just going to say max of salary and then i need to sum it with 10 hike on this salary right so how do i find a 10 percent offer salary just by using simple mathematics i'm just going to multiply this by 0.1 i'll put this entire thing inside the parentheses here so it does not get messed up so this is my salary and then this is just 10 of that salary right so this will basically be like yeah it's going to be the 10 percent over the maximum salary that is returned from this table right so this is what we are doing i hope this is clear we are just using a correlated sub query for every department from this employee table i am going to find the maximum salary by referring to the employee history table and then over that i am giving 10 hike and i am summing them together right now what are my filter condition my first filter condition is that i need to only give this hike to those employees who are working in the bangalore location now i know that this location information is only present in the department table right so what i'm just going to do is i'm going to say e dot department name okay in select department name and maybe i'll just say from department and okay maybe i'll just move this to the same line here okay and here i'll just say where location equal to bangalore okay so this is basically my where condition or maybe i'll just move this to the next line so just it becomes more clear and this is not only filter condition that i need to apply because i have another filter condition which says only consider employees in the employee history table right so what i need to do is i just need to add another check where i'm just going to say e dot employee id in and i'm going to say select employee id from the employee history table right so i think that's all so if i execute this now and you can see that it's updated 20 records okay so out of the 24 employees looks like only 20 employees satisfied this condition and that is what is updated okay so this is basically how i can use an um sub query in a update statement now you can see that i have used a sub query in the set clause as well as i have used the sub query in the where clause so you can use a sub query in the set as well as in the where clause in an update statement it's not mandatory that you need to use in both the places any one place also you can use but this is basically how you can use a sub query okay now if you think that this is not a sub query this is just one query it's not the case because this outer update statement is basically treated like an outer query and this inside select statement is treated like a sub query okay in this case i am using a correlated sub query here and then here i think this is going to be just what is it exactly okay this is like a multi row sub query but a single column multiple sub query okay so i hope this is clear now let's move on to the last part of this video and that is how to write sub queries using in the delete statement okay so let's try to understand this again by solving a particular problem and the problem that we are going to solve this time is going to be this that is let's say you have been given a requirement to delete all the departments who do not have any employees so i need to delete the departments who do not have any employees now i can just use the command like delete from department where department name in something right now this something is basically the departments which do not have any employees now i think already in the previous statement i think when we were looking at correlator sub query i wrote this query here and i'm just copying and pasting them here so this was the statement or the query that we wrote basically what we were doing is we were using a correlated sub query to find out the departments who are not having any employees right this one i already explained about i'm not going to explain it here again but this time instead of fetching all the columns from the department table i'm just going to fetch the department name okay so if i execute this you can see that it's fetching in these two departments and these two are the only departments which do not have any employees so i'm just going to use this same query here so i'm just going to cut this and i'm going to place it here okay so if i just move this to the right and let's say if i just make it more neat so there are sub queries inside the circuitry in this particular case but that's fine so i think that's all so this sub query here is going to return me the two departments who do not have any employees and once i get these two departments i am just going to delete the data from the department table which belongs to these two department that i got from this correlated sub query here okay so if i execute this it should delete two records and that is what it has done okay so this is how you can use a sub query in your delete statement okay so i think finally we are done with this video i think it was a pretty long video but i really wanted to cover everything about sub queries in a single video i hope you found some value from this video if you did then please make sure to give me a thumbs up on this video and also if you are not subscribed make sure to subscribe to the channel also leave your feedback in the comments below because i really like reading those comments it kind of makes me understand what i'm doing whether i'm doing the right thing or doing something wrong kind of helps me to improve thank you guys and see you soon in the next one bye

Channel: techTFQ

Views: 952,280

Keywords: SQL Subquery Tutorial, Subqueries in SQL, sql sbqueries, sql subquery, subquery tutorial, subqueries tutorial, sql subqueries tutorial, sql tutorial, sql, subquery, subqueries, sql subqueries, sub query, sql query tutorial, subqueries in sql, sql subqueries explained, sql subqueries for dummies, sql subqueries interview questions, sql tutorial advanced, sql subquery tutorial with examples, sql subquery tutorial, learn sql, sql tutorial for beginners, advanced sql

Id: nJIEIzF7tDw

Length: 70min 43sec (4243 seconds)

Published: Tue Feb 01 2022