What is a Subquery in T-SQL

All right. Welcome back. Now let's dig right in and try to figure out what a subquery is. All right. So what is a subquery? Well, quite simply, it's a query within another query. So let's put it all together and just try to figure out what I just mean by that simple statement. So let's start with a simple query. Let's say I have a query where we're trying to figure out vacation hours by job title. So here we have a query and we can see the results for that simple query. And then I have another query where I'm selecting the average vacation hours by employee, and you can see that just comes back with one result, 50 hours. All right. So let's put the two results together. So my idea here is I want to show by job title vacation hours and then also plop onto the side the average vacation hours. Now, what I could do is have a query and then just type in the 50 as a literal value because I already ran the query. I know what the average vacation hours are, but wouldn't it be cool if I could query the average vacation hours and somehow include that in with my query? And that's what we're going to do because we're going to use a sub query to do that because it's really just a query in another query. So let's check out how that really plays out here. So we have our first query, which is in this darker tan box. And then the second career we talked about, which is the average vacation hour in the lighter tan box. And you can see they live together now in the blue box. And this is what we will be learning about, is how to build these larger queries in the blue box. And what I want you to really focus in on today is mainly for this lesson is the fact that there's this red circled part, which is the sub query. And what it's really doing is going out and getting the average vacation hours as a separate query and then using that result as part of our larger query. That's the magic of the sub query. It's a query inside the larger query. So for now, that's all we really need to know because we're going to go into detail on all of this. But right here we're just going to learn the concept. So some queries are really versatile and they can be used throughout your query in many different locations that can be used in the select clause, like in the column list, like we just saw, we can use it in the where clause for filtering or in the from clause as part of the table or in having. So we want to do it grouping rows. We can use it to help filter out some of those groups and that is what can make some queries like Uber confusing because there can be used everywhere and I think a lot of times people have a hard time getting their fingers around them so it can be in so many different locations. It gets confusing to how they can get used. But really the the concepts are very simple. And as we go through the different lessons, you're going to see how they're applied and you're going to see that the concepts really aren't that hard. And that does make the queries hard to understand because they're just not in one place and they have so many uses. But that's what makes Subquery so cool. They have so many uses. So let's look at a subquery to select list here. You can see we're selecting from an athlete their age, their sex, their average weight, and then we're also selecting the average weight of people from all athletes. And then here's a subquery in a where class where we're saying, let's get the average weight of athletes who play golf and where their weight is greater than the average weight of all athletes. So we don't need to understand how this works quite yet, but just be aware that here we are sneaking in that sub query. And one hint you can tell that this is a subquery as it's that it's in parentheses because that needs to happen. That's a rule with sub queries and here's a sub query and having cos again see those parentheses that's kind of our flag that it's a sub query. And here we're saying where our average weight for our group of athletes is greater than the overall average weight of athletes. So here we got several scenarios where we're using sub queries throughout our query. We're again, I don't want I'm flying through this right now and the reason is, is I'm trying to just show you there's lots of places where we can apply this sub query and we're going to go into this in detail. And the idea for you right now is just to get a feel for the context of where this can be applied and have a sub memory of how it's used so that in some contexts, so that when we talk about it, you go, Oh yeah, I remember we talked about this is a place where this could happen. So here's another example of what a subquery looks like, where we're looking at selecting the product ID, the name and the list price from a product where the list price of our product is greater than the average list price of all products. See, so here's like a query on to itself. It's inside our larger query that makes it a sub query. It's a query. Inside a query. It's surrounded by parentheses. And let's break down to see how this really would work in real life. So what's happening here is in the first step, what we're would be doing if we were the actual database management system is we would run out and we would run the query and go, All right, let's go out and select the average list price from the production product table. In this case, it's from the Adventure Works database, if I recall. And it's going to return a value of like 438.6662 as the average list price. And then it's going to like keep track of that number. And then from then on it's going to go, all right, well, let's go and find all products where their average price is greater than the 438.6662. And if that passes and it's true, then we're going to include in the result. That's how the subquery works now. I very well could have myself personally gone out and ran this query and said, Hey, let's go out and get the average list price and going, Hey, check it out. It's 438.662. And then I could have typed out my own query just like I have here in step two and got the same result. But what's the fun in that? Because the minute somebody changes the list price of a product, my query is out of date. Right. So it's not as flexible, it's not as robust or as I'd say, it's a little brittle. So the sub query makes it so that our queries are are more flexible and it really takes our ability to write SQL to the next one. So the next couple of lessons, we're going to actually start looking at how to write sub queries and explore, and we're going to go through all the different aspects of how you can use sub queries and the select statement and all the different clauses. So I'm really excited that you are jumping into the class and look forward to teaching all of this to you.