What is a Subquery in T-SQL

Kris Wenzel
A free video tutorial from Kris Wenzel
Let me teach you SQL using simple to understand English.
4.5 instructor rating • 2 courses • 6,705 students

Lecture description

After completing this course know what subqueries are, and how to recognize them within a SQL query.

Learn more from the full course

Subquery Magic: Write awesome SQL, Master T-SQL Sub Queries

Take the mystery out of subqueries & take your SQL to the next level. Use SQL Server & write data driven nested queries.

02:47:13 of on-demand video • Updated April 2020

  • Write subqueries and use them in various parts of a query, such as the FROM, WHERE, and HAVING clauses.
  • Gain the confidence to know when to use a sub query.
  • Solve complex problems, such as incorporating one query's results, into another's filter criteria.
  • How to write a correlated subquery (this is a big deal)
  • Solve problems, otherwise impossible to solve using SQL alone, using sub queries.
English [Auto] Welcome back in this lesson we're going to learn what is some queery. So what is a sub query. Well simply put a sub query is a query within another query. So let's start with a simple example. We're going to start with a pretty simple query here. I have a query where I am selecting job titles for employees. There are vacation hours from the venture works human resources employees table and then I have another query where I'm getting the average vacation hours for all the employees from human resources. So can we combine these two queries together. So I have the results from the first query and then the average vacation hours of course we can by using a sub query. So here you can see the select statement and in red here is the subquery. So if you recall in our original query we were selecting the job title the cation hours from human resources employee and then and the second query we said we wanted to get the average vacation hours for all employees. Well here you can see that we have in parentheses that slack statement when a select statement is in parentheses it's a subquery. So the trick here is is that since our cell query is part of the slack costs and needs to bring back a single value such as an average as this one does. And since that is doing so we can add as a column value to our query. So here's a really simple example of using a sub query and our select clause. The statement that is circled in red corresponds to the values that are brought back so here's that same query again. But now we're looking at the queries as they are separately listed as taxed. So here was the original query for the job titles and vacation hours. Here was the average vacation hours queery that we had. And again here are the two queries put together into one with the query highlighted with the rad. So again a sub query is simply a query with another query. In future lessons will actually go and write these queries. But for now my goal is just to show you what these queries look like and for you to recognize them and for you to also understand that they're not mysterious. So some queries are very versatile. Some queries can be used throughout your query in locations such as the slack clause as we have just seen. They also can be used in the WHERE clause. The FROM clause and the having clause with all these locations. You can find some queries it can make them confusing because the kind of a looser because when you look at a statement and you find these sub queries you're at it can seem confusing because they have a lot of different uses and they affect the query results in different ways. And I think that's where the mystery of the subquery comes from and we will certainly untangle that through this course so I totally understand that. That can make so Clary's hard to understand because they just aren't in one place and they have so many uses. So I want to show you some of those areas just so you see where they are. And again this isn't so that once you see it that you could go out and write the sub queries but just so you can start recognizing those areas and get comfortable seeing this query so that later when we learn about the query they will look so scared. So here's a submarine select list and we just did this earlier. So here we are selecting the age the sax and the average weight from an athlete but also the maximum weight for all athletes. Here's that same sub query. But now in the WHERE clause. So here's the WHERE clause and here's the so query and read and again you can see where that subcarriers now found itself in the having classes. It's the same sub query can be used in different places. In this case it may look scary but what this really is doing is just pulling back a single value from athlete for the average weight and then using that to compare to the average weight found for a group of athletes by age and sex. So let's go back and kind of look at the sub query again and kind of walk through one. So here's another example from aventure works database where we're going to select the product ID the name and less price for a product and we're going to get our products who have a less price that's greater than the average or less price of our products. All right. So if I wanted to do this query I could go about it by first going out and running a query and query tool and get the value of the average products which is 438 point 6 6 6 2. And then use that number and plug it in to my select statement. But the issue is is that if we ever add or remove products from our database this average price will change. Beauty of the sub query is that it allows us to kind of adapt to the new average price for products makes our query more robust and allows us to avoid having to hard code the average price into our queries. So that's a big benefit to having. So the Hopefully by seeing how we have these steps here you can see that if you were to manually do this or query that you understand the steps that the computer uses to actually run the sub query first would actually go out and run this query to get the average get the 438 point 666 to value take that value and then substituted in for everything on this red box and then basically run the query select product ID name was price from production dot product where it was price is greater than four hundred and thirty eight point six six six six to a couple of key points. They want you to keep in mind when we're talking about some queries versus queries just a select statement inside of another some queries are always enclosed in parentheses a sub query that returns a single value can be used anywhere that you would use an expression. So what I mean by that is a subclass that returns a number or a text value can be used anywhere where you would be using a mathematical equation or you would be expected to provide a input to a formula such as uppercase or even a column value. So that's why our subs clearly that returned a single value of average could be used in the SWAC statement because expecting an expression which is the combat so query that returns more than one value is typically used in a list of values such as those and an operator. And we'll talk about that later. But I think right now it's important to understand that when we're running a sub query some queries bring back one value and others can bring back more than one value. And when you run queries that bring back one or more rows they can cause errors if they used the wrong place. And again that is what can cause issues with understanding such queries and we will go into detail about when and how those are used. Also keep in mind that those sub queries are very powerful and can be used to really solve the difficult problems some queries can also be somewhat inefficient. So you always want to be careful when using them. And when we get into some dangerous situations I will show you where those are and how to avoid overtaxing the computer when running so of course. And just in case you were curious if there are limits to how many sub queries you can have in your ask you at this point you can rest up to 32 levels of sub queries. So in my mind that's quite a bit of queries. So you could have an ask you or statement that has an Ask you say but that an Ask your statement on and on and on 30 times deep which I think is a pretty complicated statement and I think that would be pretty hard to read. I can't imagine actually writing one that I've seen maybe three or four deep and that's been pretty confounding in my mind. So I think we're covered with 32. So if you have any questions regarding this lecture please come in the notes I or someone else can get back to you and I'll in the next lesson.