hey welcome the draft Academy my name is Mike in this tutorial I'm gonna talk to you guys about nested queries in SQL a nested query is basically a query where we're gonna be using multiple select statements in order to get a specific piece of information so a lot of times we're gonna want to get very specific information and we're gonna need to use the results of one select statement to inform the results of another select statement so this is a little bit more advanced and this is kind of when we're getting into more advanced query writing but I want to show you guys how this works because a lot of information that you're gonna want to get is gonna involve using nested queries so let's go ahead and put a prompt up on the screen it says find names of all employees who have sold over 30 thousand dollars to a single client so we want to get the names of the employees if they've sold more than 50k to a client so first thing I would do if we were trying to figure this out figure out how to write this query is let's just look at the information that we have so down here we have this works with table and the works with table has total sales right and each one of these rows defines how much a particular employee has sold to a particular client right so employee 105 sold $55,000 to client 400 et cetera right so over here we have part of the information right and in other words here we have the total sales but what we don't have is the employees first name and their last name right we don't have the actual employees name what we do have though is the ID of the employee who did it right so we have the employees ID and we can use the employee's ID in order to get their first name and their last name so in this case we had part of the data here on the works with table and we have part of the data up here on the employee table and this is a situation where we can use a nested query so the first thing I'm gonna do is I'm gonna write a query which is going to get me all of the employee id's that have sold more than thirty thousand dollars to a single client alright so we're gonna start with step one which means we're getting all of the employee IDs from here if they've sold more than 30k so let's go ahead and write that query shouldn't be too hard considering all the stuff that we know so I'm gonna select employee underscore ID from works with and I'm gonna select it where total sales is greater than 30,000 and we'll go ahead and end this and actually up here I'm just gonna prefix this with the table name so I'm gonna say it works with dot employee name and then down here we'll say works with dot total sales just so it's more clear especially when we get into nested queries it's usually useful to prefix everything with the table name just in case we have a repeated column name so over here I'm gonna run this and this should give us all the IDS of the employees who have sold more than 30,000 to see if we get 102 and 105 shows up three times so it looks like 105 has sold a lot of paper and so now we have all of the IDS of the employees who have sold more than $30,000 worth of products and so what we can do now is we can figure out from this information we want to get those employees first names and last names and so I'm gonna go ahead and write another query up here I'm gonna say select and I'm gonna say employee dot first name and why don't we do employee dot last name and we're gonna select this from employee and then over here we're gonna say where and this is where we're gonna go ahead and use a nested query so basically I want to select all the employees whose IDs we got from this query right here so what I can do is I can say employee EMP ID in and remember the end keyword is going to give us a result if the employee ID is in values that we specify inside of these parentheses so what I could do is I can actually nest this query inside of there so I can go ahead and take this and I can just paste it right in here in between these parentheses and one thing you want to keep in mind is just how this is formatted so you'll see I formatted this and it's indented from this query over here and then I'm gonna get it rid of this semicolon and we'll put a semicolon over here so basically what this is saying is I want to get the first name in the last name from the employee table where the employee ID is in the result of this query so if the employee ID got returned from this query which gave us the IDS of all of the employees who mate who have sold over 30,000 then we're gonna return their first name and last name so let's go ahead and run this and you'll see over here now we're getting the names of the two employees so Michael Scott sold over 30,000 and Stanley Hudson also sold over 30,000 so that is actually a really cool way where we can find out that information so that's how we can use a nested query right a lot of times you'll use this in keyword I'll show you some other examples where we use other things but in that case we're basically checking to see if the employee ID is in this result all right so now I have another prompt here it says find all clients who are handled by the branch that Michael Scott manages so this is another interesting one it says assume you know Michaels ID so we're gonna assume that we know what Michael Scott's ID is this is another one where again we're gonna need to grab data from one table in order to inform the data from another table so the first thing that we want to be able to do is figure out the branch ID of the branch that Michael Scott manages right so over here we have our branches and each one has a manager ID right and so what we need to do is be able to figure out which of these branches Michael Scott manages then once we have that information we can figure out all of the clients that use that branch ID right so over here the manager ID will map us to Michael Scott and the branch ID will actually map us to the client table over here because that has a branch ID as a foreign key so the first thing we'll do is we'll figure out what branch Michael Scott manages so that should be easy enough we can just say select and actually we'll just do the branch branch ID from branch where and remember we we're going to assume that we know Michael Scott's ID so I can just say branch branch ID is equal to and Michael Scott's ID is 102 so I can just say is equal to 102 and so what this should do is it should give us the branch ID of the branch that he manages in this case and actually whoops instead of branch ID this needs to be manager ID and this is going to give us two right because two is the Scranton branch which is the branch that Michael Scott manages so now that we have this piece of information all we want to do is just get all of the clients that are handled by that branch so we can just say select and why don't we just get the client name so to say client the client name from client where and over here we're basically just gonna say we're client dot branch ID is equal to and over here we're going to set it equal to the result of this query so we're gonna set it equal to the result of getting the ID of the branch that Michael Scott manages so down here we'll put this statement and you'll see again I'm just embedding this in here so what's gonna happen is when the relational database management system sees an embedded sequel statement like this it's going to execute this first and then it's gonna execute the outer one so it starts in ER and then it slowly goes out ER so we'll be able to get the branch ID where Michael Scott is the manager and then we can use that information to find all of the clients so over here I'm just gonna think run and I'm gonna go ahead and get rid of this semicolon right here and now we can go ahead and run this and you'll see we get all of these clients so we get like Dunmore High School Lackawanna County Scranton white pages and FedEx so those are all the clients that are managed by the Scranton branch now there is one more thing I want to point out which is you'll notice down here we're setting client branch ID equal to we're checking to see if it's equal to the result of this statement but here's the problem is this statement isn't necessarily guaranteed to only return one value so if this so let's say that Michael Scott was the manager at like multiple branches it's possible that this would return simple values so what we want to do is just come down here and say limit 1 and that'll make sure that we only get one of these so now if I click run you'll see it does the same thing although now we're just making sure that we only have one so anytime you're using something like equality it's always a good idea to limit it down to 1 unless you're looking for a situation where Michael Scott's gonna be managing multiple branches in which case we can use in instead all right so that's kind of a little dip into nested queries now obviously these can get very complex and really you know what's important is that you have a solid understanding of the fundamentals if you don't understand the fundamentals that we've kind of talked about up to this point in this video then using nested queries is really going to confuse the crap out of you all nested queries are is it's just kind of like one query informing another query maybe informing another query right we just use the results from one query to get results from another query etc and as long as you can break the nested query up into its individual parts you should have absolutely no problem writing these and really the best way to get good at writing more complex queries like this is just a practice so the more you practice writing nested queries and using all these things in combination the better you're gonna get at it hey thanks for watching if you enjoyed the video please leave a like and subscribe to drop acad to be the first to know when we release new content also we're always looking to improve so if you have any constructive criticism or questions or anything leave a comment below finally if you're enjoying chopped Academy and you want to help us grow head over to draft Kadim EECOM forward slash contribute and invest in our future
