Advanced SQL Tutorial | Subqueries

what's going on everybody welcome back to another sql tutorial today we are going to be talking about subqueries now subqueries are often called inner queries or in 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 subqueries 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 in 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 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 sub query so i'm going to select and really quick actually let me copy this so we're going to do employee id there we go we're gonna do salary and now we can start building our subquery so we need to do an open parenthesis and i'm just gonna copy this really quick because we're gonna be doing it off of that table so we're gonna 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 and 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 look a little prettier um 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 uh 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 sub query 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 sub queries 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 sub query and let's paste this in here and 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 so 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 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 parenthesis 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 sub query 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 in the aware 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 in the next video [Music]

Channel: Alex The Analyst

Views: 22,812

Rating: 4.9892616 out of 5

Keywords: Data Analyst, Data Analyst job, Data Analyst Career, Data Analytics, Alex The Analyst, subqueries, sql subquery, sql subqueries, subqueries in sql, subquery in sql, advanced sql

Id: m1KcNV-Zhmc

Length: 8min 37sec (517 seconds)

Published: Tue Apr 20 2021