 Hello everybody. In this lesson, we're going to be taking a look at subqueries in MySQL. Now subquery is basically just a query within another query. We can do this in a few different ways and I'm going to try to show you a lot of the different variations within this lesson. The first way that we're going to use a subquery is in the where clause. Then we'll take a look at the select and the from clause also. Let's take this demographics table that we have down here. What if we only wanted to select the employees who worked in the actual Parks and Rec department? Well, we could do that if we had a few joins. We have the salary table and one actually represents that they work for the Parks and Rec. If we come over here and we open this up, we can see that Parks and Rec is the department ID of one. So we do have that option. We could just join these two tables together, but sometimes we don't want to do that and we'll use a subquery. Let's see how it works in the where clause. So let's go ahead and get rid of this. So what we're going to do is we're going to say select everything from Employee Demographics where and now we want to pull because this is the salary table. We want to pull employee IDs where the department ID is equal to one. Remember we're querying off of this table. So let's actually pull this up. This is what we're working with. So we want to say where the employee underscore ID that's referencing this column in the Demographics table is in. What we're going to do is we're going to do a parentheses here and we can even come down and put a parentheses down here. So what we're going to do now is write our query, which is our subquery and this is our outer query. So now we're going to write an entirely other query within this. We'll say select and now we're going to say employee underscore ID and let's just bring this over. I usually have it something like this and I'm going to try to bring this down a little bit. So select everything and then we'll do from and then instead of Employee Demographics, we'll do employee salary and let's just format this a little better. So select the employee ID from employee salary and remember we wanted to do where the department ID is equal to one. Now let's bring this back up and this is what the query is going to look like. Now just by itself let's run this subquery or this inner query. When we run this, it's going to create this list of just employee IDs where the department ID is equal to one. So when we say where the employee ID from the Employee Demographics table is in, it's going to try to match those employee IDs to this list of employee IDs. So just remember 1, 2, 3, 4, 5, 6, and 12. Let's go ahead and run this entire query. Now we have 1, 3, 4, 5, 6, 12. If you remember from previous lessons, the 2 is Ron Swanson and he's only in the salary table. So since we're doing just the Employee Demographics table, he's not in here. So what we're doing is we're selecting everything from the Employee Demographics where the employee ID in this table matches or is in the select employee ID from the salary table where the department ID is equal to one. In essence this is what a subquery is. It's a query within a query. Now what would happen if we have the employee ID but we also wanted to say the department ID because we just wanted to view this. Let's go ahead and try to run this. We are going to get no output and we're going to get an error that says operand should contain one column. The operand referring to this entire thing right here because this is an operator. So this is our operand and we're returning two columns in here which is saying we cannot do. We have to only have one. So now if we run this, it works perfectly well. Let's bring that down. Now we can also use the subquery in a select statement. So let's take a look at that next. Let's go down here and let's say we want to do select everything from employee underscore salary. And let me spell that right. Let's say we want to look at all the salaries just like how we have it now but in a column next to it we also want to compare it to the average salary for everyone. So we'll be able to see whether somebody's salary is above average or below average. So what we would try to do potentially is do something like first underscore name salary and average salary. And we try to run this and of course we're going to get an error. It's going to basically tell us that we need to group by if we're doing this. So let's go back down and let's actually add that group by. And we'll say group by first name and salary. And we'll look at this output. And this is not looking good at all. It's just looking at the average salary for each unique row which is Leslie 75,000. So the average is 75,000. This is not what we're looking for. This is not what we want. Here's what we really do want. We want to just take the average salary of this entire column regardless of group by or anything else. So let's get rid of this and let's see how we can do that. So let's come right down here. We're going to say select, select the average salary and then we're going to say from out of parentheses because this is our sub query from the employee salary table just like that. Now if we run this we should get the exact output we're looking for. So the average salary is 57,250 and we have our salary right here. So we can compare really quickly just like that. Now we can also use a sub query in the from statement. So let's go down here and let's say select everything from employee underscore demographics. Let's have it autocomplete for me. So we have the employee demographics table. Now let's create a group by based off the gender column and add some aggregated functions and I'm going to show you how you can use this as a sub query. Let's go up here. Say gender and then we'll go ahead and add our group by so say group by gender as well. Now let's add a few things. We'll do average. We'll do average age and then we can do let's just do all of them based off the age. We'll just do age, min of age and count of age. When I try to write fast that doesn't always go right. So we have this let's run this and this is what our output is going to look like. Now what if we wanted to get the average of the oldest age or the average of the smallest ages or you know see what the average count is for males and females. Well we can't do that given this table but let's do something right here. Select everything and I'm going to say from and in our from statement we're going to have a parentheses. We're going to paste our select statement and then close the parentheses. So we're going to select everything from this output that is right down here. So if we run just this we're going to get an error and forgot this was going to happen but every drive table must have its own alias so you have to name a table. I forgot it does that all we have to do to fix this is just name it so we'll say as and we'll say aggregated table we'll just call it aggregated table. So let's run this and we get the exact same output but here's the neat thing is we can now select we can do gender and these are actually the column names now so I can do the average of this column right here but I can't do it just like this because it's going to give us an error and I'll show you why in just a second. It says unknown column age in field list so what it's saying is is we're trying to perform an aggregated function on the aggregation of an age column but we don't have an age column in our table right here let me run this again. We have a column named this exact thing so what we actually need to do is do this back tick and back tick this is the actual name of the column it's not an aggregation anymore the back tick on my laptop is right above the tab on the far left hand side right under the escape that's where mine is so these back ticks it's not a quote like this it's a back tick so you just need to find that on your keyboard but now if we run this it looks like we encountered another error says in aggregated query without group by that's right now we need a group by so now we need a group by gender here sometimes you got to figure this out on the fly and it should work there we go so now we can perform aggregations on this table now this doesn't actually work perfect because we're still grouping by the female male but let's get rid of this for a second and we'll get rid of this group by entirely and if we run this we're now looking at the averages of this column right here max age now when you're doing something like this it's actually really smart to rename these we'll say as average underscore age we'll say as max underscore age and it makes it so much easier you won't have to do these back ticks anymore and as min underscore age and so on and so forth and I would probably format this better and stuff like that we don't have to go through everything right i'm just going to give you an example but then when we're using this table these columns are actually named this so I don't have to do these back ticks anymore I can just take this whole thing oops give her that back tick now I can just take this column because this is the column name so let's go ahead and run this and it's still going to work perfectly so this one's pretty cool because you're basically creating this kind of like a temp table you're just creating your own little output then you can query off of it and you could do you know more advanced calculations this way it's actually really useful but there are better ways to do something like this like a cte or a temp table that will look at in the advanced series but this is at least how you can do it and you can actually try it out using subqueries so that is all we're going to look at today for subqueries in the next lesson we're going to take a look at window functions