 What's going on everybody? Welcome back to another SQL tutorial. Today we are going to be talking about sub queries. Now sub queries are often called inner queries or nested queries and they're basically a query within a query. A sub query is used to return data that will be used in the main query or the outer query as a condition to specify the data that we want retrieved. You can use sub queries almost anywhere. You can use it in the select part of a query, the from the where. You can also use it in insert update and delete statements. But in today's tutorial we're only going to be looking at the select the from and the where statements and you should get a pretty good idea of how to use it in those other statements. All right now I'm going to paste on screen basically what we're going to be walking through today but really quick let's just take a look at the table that we're actually going to be working in and that is going to be from the employee salary table and I just want to show you the data that we're going to be working with before we actually get into it. So we have an employee ID, we have a job title and then we have a salary. So really quick I'm going to show you what it looks like to have a subquery in the select statement. So let's go down here really quick and what we're going to try to do is kind of do something like a windows function but without actually having to do the windows function and so we're going to do this with a subquery. So I'm going to select and really quick actually let me copy this. So we're going to do employee ID, here we go, we're going to do salary and now we can start building our subquery. So we need to do an open parentheses and I'm just going to copy this really quick because we're going to be doing it off of that table. So we're going to say select and then I'll paste that and close it as well but what we want to do is we want to say average and salary. Now what this is going to do is it is literally going to run this and let's run this really quick. It is going to run this and it's going to show that the average salary for all the employees is $47,909. So we are looking at the average salary for every employee. So when we run this is going to give us the employee ID, the salary and then in the very last one is going to show the average salary for every employee. Now it doesn't have a column header so or column name so let's give it let's say as all average salary and we'll run that one more time just to make it a little prettier. You can also do this in partition by I'm going to super quickly just really quickly write this out it should take no time at all and then I'm going to show you why we can't do this without the subquery why you aren't able to do this with a group by. So really quickly let me copy this I'm going to put it right down here and we're going to say average salary whoops and we can get rid of all this and we can say over and we're not going to partition it by anything but let's run both these at the same time you'll see that they're the exact same outputs and so it's just a different way of doing it in this example but it really is just to show a comparison of how you might be able to use a subquery in the select statement. Now you might be wondering why group by does not work for this really quickly I'm going to write this out and let's get rid of that and we'll say group by whoops let me at least try to write it correctly group by and we'll do employee ID and we also have to do salary and then we'll say order by one two so let's run this and as you can see since we have to use the group by groups by both the ordered ID and the salary and so we're not going to be able to get that all average salary that we're looking for that we can get in the partition by and also the subquery in the select statement. Now I'm going to show you the subquery in the from statement so let's just get rid of that really quick and let's say select everything let's say from and we're going to do an open parentheses here and here is where we're going to write our subquery so if you have watched previous videos where I've done tutorials on the CTE or tutorial on the temp tables this is one that is very much like those except I think a little bit less efficient when I'm doing something where I'm creating a table and then querying off of it which is what we're about to do I much prefer a CTE or a temp table subqueries tend to be a little bit slow compared to a temp table or a CTE I tend to use temp tables a lot more because you can reuse them over and over whereas a subquery you cannot you have to write it out each time so really quickly I'm going to show you how it's done although I don't really recommend using this method really quickly let's go up here and let's steal this partition by really quick this will be our subquery and let's paste this in here make this look a little nicer so you can visualize it a little bit easier so really quick what this is going to do is it is first going to run this and create this table again much like a temp table or a CTE so let's execute this really quick it's going to create this table and then it's going to allow us to query off of it so I can actually say and let me give kind of an alias to this a dot employee ID and then let's say all average salary so now I can take columns from this inner query if I want to and just select those or I can select everything and return that entire table again I much prefer a temp table or a CTE for this type of situation but as an example I just wanted to show you how it works now let's go down to the subquery in the where statement but really quick I just want to steal this query so I don't have to rewrite everything and let's get rid of this really quick and add back the job title all right let's look at this really quick so we have our table that we've been using our employee ID job title salary so for this example we only want to return employees if they're over the age of 30 and as you can see in this table there is no age column that is in the employee demographics table now if we wanted we could join to that table and get that information or we could use a subquery and so for this example we are going to be using a subquery so let's go right down here and say where employee ID is in and we'll do an open parentheses and now this is where we are going to build out the subquery so just for visual purposes I'm going to go right here I'm going to say select everything and we'll do from employee demographics and close the parentheses so we're going to try to select something in this subquery that will then identify the employee IDs that are over the age of 30 so really quickly let's take a look at this table so right now we have the entire table selected so we have the employee ID first name last name age and gender so in this subquery the only thing that should be returned is the employee ID and in fact in your subquery you can only have one column selected so I can't select everything I have to specify one column and that's a little bit different than how we did it in this from statement where we were basically able to select the entire table and then in the select statement specify what columns we wanted anywhere statement we can't do that so we want to return the employee ID and we also want to say where the age is greater than 30 so let's run this really quick and see if it works as you can see in the results these are the employees who are over the age of 30 now if you wanted to display the age as a column in this output you would have to join to that table and then put that column or that field in the select statement but in a lot of situations you won't actually want or need to do that and so a subquery can be a really good option in these scenarios with that being said this is the last video in the advanced sequel tutorials I hope that this series has been helpful and that you learned something along the way thank you so much for joining me I really appreciate it if you like this video be sure to like and subscribe below and I'll see you in the next video