Hey everyone, welcome back. In this video, part 18 of topic 3 in our database class, I'm going to show you how you can use sub queries in the structured query language. Let's get started. Now, we have reached a point in our study of the structured query language, where we can start to learn to pull data simultaneously from multiple tables, which can be extraordinarily useful. And there are two different ways that we do this. The first one that we'll learn about is called a sub query. And the second approach is called a join. So sub queries are interesting because the basic idea here is that we are going to use the results of one query as the input into another query. Okay. So we know that when we get a result from a SQL select statement, that result is a relation, right? It's a table that meets certain properties. And we can then use that result, that relation, as input into a second query. Okay. And in this case, we are writing sub queries and there are two different types that we'll learn about. Non-correlated versus correlated sub queries. So hopefully this will be entertaining. And we'll begin by taking a look at non-correlated sub queries. And I'm really sorry for the language here, like it's here, these technical terms, I'm like, yeah, you think they could have thought of something nicer to describe it than a non-correlated sub query. But we'll take a look at it as long as you get the concept. It's not so important that you memorize all this crazy technical jargon. And again, we'll begin with the non-correlated sub queries because they're the easiest to understand, I think. Now remember, generally the concept with a sub query is we're going to use the input from one SQL statement or the output from one SQL statement as input into another. And you can see here that we have a select statement here in closed in parentheses. And this is our inner statement. And then we have another select statement here. And this is our outer statement, our outer query. So we have the inner query. And we have the outer query. And you can see that this select statement will return some results. So let's just focus our efforts exclusively on this one for now. And we'll say, what does this do? So this is asking for department IDs. So the result then will be a list of department IDs. And the database is told to look in the department table. And specifically the department IDs in which we're interested are those associated with department names that begin with account. Okay. So this could be accounting. It could be accounts payable. It could be accounts receivable. Whatever it may be, if we have those departments in our company that begin with account, their department IDs will be returned from this inner query. All right. So we'll get these department IDs back. And then this list of department IDs, whatever the answer is to this query, this list of department IDs is then used as input into the outer query. So you can see we have an in keyword here. So let's say that the result of this is, I don't know, the numbers one and four. So department one and department four. So essentially what's happening here is this inner query gives us this result. And then that result here is used as or substituted at this point in the equation so that it can earn in the statement so that it can be used by the outer query. So effectively what this turns into then is something that looks like this with these particular numbers that I'm just arbitrarily chosen. It would look like one and four and then we can imagine that just for the sake of just to help us all understand this. So essentially what we get then is something that looks like this. Right? Our inner query has done the hard work and has determined that one and four are the results there. And it's just used as input here. So essentially we're then running this simple query with the input that we extracted from our other query. So interesting and the other thing I wanted to point out here is that we are whether you notice this or not. Pulling data here from the employee table and here from the department table. So it's a sub query and in this case we have to pull information from two different tables in order to be able to answer the entire query. That is the inner query. Pulls information from the department table. And then the outer query pulls information from the employee table. Now this is a non-correlated sub query and in a non-correlated sub query this inner query, this inner select statement here, only needs to run one time in order for the database to answer the entire thing. So you'll see I'm just going to get a fixed answer from this. Right? I'm going to get a set of department IDs like one and four. And that only has to run once. In order for me to be able to answer this part of it. So this is a non-correlated sub query. Basically the sub query, this part here, the inner query, only runs once. So let's see an example. Here, what's C? What do we want to do? Maybe I can do something similar. Just as a reminder, let's take a look at our department table and see what's in there. Right? So we'll just give me everything out of the department table. And you'll see we have some departments in here, District 12, District 4, District 7, and the capital. So with that in mind, let's write ourselves a little sub query. Now if you remember, if we take a look at our employee table, like it's spelled in this table, we have department IDs, but no department names. So let's say that we are unaware of what the names associated with these individual department IDs may be, but we would never let want a list of employees that work in a specific department by the name of the department rather than the department ID. So in that case, what I could do is something like this. We'll say where the department ID equals. And then I'll go ahead and write myself a little inner query here. Well, I'm just going to select the department ID from actually, I'll format this according to our usual approach so that it looks familiar. This has got to come from the department table. And I forget what did I call that attribute to just name is the name out there. Let's say I want these or District 12. Name in my department. So if you recall, we can run a single statement by highlighting it. So I'm just going to select this part. Now this is our inner query. When I run this, you'll see that we get a number. The department ID is two. So that is the department ID for District 12. And that value then is used as input into our outer query. So effectively what we're doing is this. It's just that we don't know what this number is. So to get that number, we have to use this inner query. This fetches the department ID for District 12. And then we use that to identify the set of employees who work in District 12. So this is effectively the statement here. And if I run this and see that we get these five employees, Katniss Primrose, Gayle, Peeta, and Heymich, so they all work in District 12. That's if we know what the department ID is. But if we do not, we want to search for it. We can do something like this. We'll see that when we run this, we get exactly the same results. So this inner query is running one time to fetch the department ID for District 12 out of the department table. That number is then used as input into this outer query to generate the list of employees who work in District 12. Okay. Now I want to build this out a little bit so you can see that you have no constraints really when writing these things. So let's say that we wanted to put, I don't know, another constraint on this where maybe we only want to the employees from District 12 who were hired in 2020 or later. Okay. So I can easily build this out and just say where higher date is greater than or equal to, let's do first or January 2020. And so you can add additional filtering criteria here just like we have in the past in order to ask more subtle questions of the database. So in this case, we're saying, give me a list of all the employees who work in District 12 12 that were hired in 2020 or later. And that's a pretty specific question. Then you can see if I run this, it gives me the results. So we can see here that PETA and Hamich were hired in 2020 or later and also work in District 12. So in this case, we have a sub query with an additional criterion in the where clause. Cool. All right. Let's learn about correlated sub queries. So here is a correlated sub query example. And in this one, you can see that as usual, we have an inner query and an outer query. And let's see if we can figure out what's going on here. Now remember in a non-correlated sub query, the ones that we were looking at a moment ago, the inner query only needs to run one time in order for the database to answer the entire thing. In a correlated sub query, this inner query is going to have to run once. All right. So say it's going to have to run many times in order for the database to answer the entire query. Okay. So this inner query, which is highlighted in yellow, is going to have to run many times in order for the database to answer the entire thing, which is highlighted in blue. And that's the difference between a non-correlated and a correlated sub query. It's simply how many times does this inner query need to run in order to answer the entire thing, which here is shown highlighted in blue. So if this inner part currently highlighted in yellow needs to run more than once, it is a correlated sub query. If it runs just once, then it's a non-correlated sub query. And the correlation that's mentioned in this notion of a correlated sub query is a connection between the inner query and the outer query. In this case, what we're doing is we have a connection here between the employee ID, I should say the department ID value that's being used in the inner query, and the outer query, which you can see here, we're referring to the employee table as E. So the department ID will come from the employee table and will be used in order to run this, sorry, this inner query here, so here. So in order to run this inner query, we need a piece of information from the outer query, which in this case will be the department ID coming from the employee table. So we're just using the letter E here as an alias for employees, so we don't have to type it all out, but we could have just as easily written employee. department ID here. So with that in mind, let's see if we can take a look at this and figure out what's going. What does this inner query do? Well, it's getting the average salary, and so we're getting an average salary out of the employee table for a particular department, and the department that we're interested in is the department of whichever employee you are currently looking at here in the outer query. So that means we have to then take a look at the outer query in order to understand the entire thing. This says, give me the employee names out of the employee table where their salary is greater than whatever the result of this is. So this is getting the average salary for the current employee's department. Okay, so let's say that we're looking at employee number one, maybe employee number one is Dan, and Dan works in the accounting department. So I want to know if Dan should be included in the results. It is, should this be a result? And the way that I determine that is I say, okay, is Dan's salary greater than the average salary of all employees who work in Dan's department. This is referring to current employee that we're looking at. So this inner query then will give me the average salary for whatever department we're currently considering. And any specific employee will be included in the results only if their salary is greater than the average of their department. So these are people that are earning an above average salary within their department. Kind of interesting.