Hey, everyone. Welcome back to part 19 in topic three of our database class. And in this video, I'm going to provide a brief overview of joins, and then I'll show you in detail how to use inner joins in SQL. Let's proceed with our next method of getting data simultaneously from multiple tables. And this is the most common type, much more common than subqueries. This is called a join. joins as the name suggests are going to join two tables together so that we can generate output involving data in those tables. So we know that our tables are related to each other in these relationships that are constructed using matched pairs of values is primary key for and key relationships. And we can use those connections among the various rows in the tables in order to join rows in one table with rows in another table in order to generate some sort of useful output. This is very, very common because as in the relational world, we store information about each individual topic or theme in just one table, but truly gain insights into our say our overall business. We're going to need information about multiple themes to be merged together in order to give us more sophisticated answers to the types of questions that we may want to ask and joins our tool of choice for this purpose. Now I'll begin by showing you Dan's topology of joins of database joins. And broadly, as we can see here, all joins can be classified into one of two types that are either going to be inner joins, which is fine because there's just one type of inner join, right. Or outer joins. So our two major types of joins are inner and outer. Generally, you will use inner joins much more often than outer joins and among outer joins, as you can see, we have three different types. So you can have a left outer join, a right outer join or a full outer join. And we'll learn all about what these different types of outer joins are, along with what inner joins are. And we'll begin with inner joins because those are the easiest to understand. So here is an example of doing an inner join where we are pulling information from two tables. And we are using the where clause to tell the database how to connect or link the rows in those two tables together. So there are a few somewhat new things here and I'll try to point them out. So let's imagine our world where we have employee names that live in the employee table. And we have department names that live in the department table. And what we want is a list. Remember, this is going to be our output. So we'll have two pieces of information in our output. That'll be the name of an employee and the name of the department in which that employee works in contrast to the idea of the department. So we want the name of the department and the name of the employee to appear in our results. And to do that, we have to look into different places because employee names live here in the employee table. Well, department names live here in the department table. So our database needs to look in both the employee and department tables. In order to be able to answer this question where our question is, hey, database, give me a list of all my employees and the names of the departments in which they work. So what's new here? Well, there's some things that you'll note that we have two tables listed in the from and they are separated by a comma. So just a comma separated list of tables. This tells the database that it needs to look in both the employee table and the department table in order to be able to answer this question. We're also seeing the use of an alias here for the names of the tables. Though instead of referring to the table by its full name, like employee, we're just going to refer to it with the letter E for the rest of the statement. Same thing here instead of referring to the department table by its full name. We're just going to refer to it with the letter D and this just saves us some keystrokes. Right. It's less typing. So, and then we actually do the join in this case in the wear clause. So we're using where here to tell it, tell the database how to link rows in the employee table to rows in the department table. Now, we know that we have a primary key foreign key link here between department ID and the department table, which is its primary key and department ID in the employee table, which is its foreign key. So we're just telling it, hey, department IDs in the employee table employee as E. So this is what this E is referring to and match those with department IDs in the department table, which we're referring to here as the letter D. So it's just going to link those tables together based on matching values of department ID and then it will know how to connect them and be able to extract the information that we need, which in this case is the department name and the employee name in order to generate our output. So let's see an example of this. So we'll replicate that. But our database here is designed a little bit differently. So let's say that we want to first name last name. And I think if I remember correctly, I referred to the name of the department just as name. So that's a little messy, but we'll include it here anyway. Right. And then we'll select those values from first the employee table and secondarily from the department table. And then I can use the where clause to link them together. Now I'm not using any aliases here. So that means I have the unfortunate duty of typing out everything. And in this case, it's going to be department ID and the employee table needs to match. And department ID in the department table. Okay. So if we run this, we'll see. And we get these results. And then we may be satisfied with this. We can clean it up a little bit if we need to. Like maybe I'm dissatisfied with that just being called name. Well, we know that we can give something alias. So maybe I change that to department name. And that will make my results a little cleaner. Right. So we know that this is the department name here. And maybe we want to continue using this notion of an alias on our tables. So we can build this out by saying as e, which is what we saw the slide as d. Right. And then this can be. Rewritten as we see here. So fewer keystrokes. And we'll get exactly the same results. Cool. And even this as keyword is not technically required. You might want to put it in there. Just. Safe, but you can do something like this as well and save yourself even more keystrokes. And again, you'll get exactly the same results. One last thing that I will mention is our list. Of columns that is currently appearing in the results here. And does not contain any ambiguity. Because the column names, first name, last name and name are not duplicated between the department and employee tables. If they were, or if we just wanted to eliminate all possible ambiguity, then we could help the database out a bit by telling it precisely. Where it needs to look to get those specific columns. So I could do something like this. And that says, get the first name out of the employee table. Get the last name out of the employee table. Get this name attribute out of the department table. And we're going to refer to that as department name. Okay. So this is a completely unambiguous way of writing the statement. Right. The database needs to make no assumptions. You're telling it exactly what you want and exactly where it needs to look to find that information. So here on this, we get our results. And of course, we can add additional things in here, but it makes it a little more cumbersome. So let's say, for example, that, I don't know, I want to do an additional filter. And limit this to, say, I don't know, District 12. So we can build out our where clause, make it a compound condition. Oh, here's a question for you. If I use an alias like this up here, can I use that alias in the where clause? So could I, for example, do it? It seems like it's a little grumpy. Oh, it doesn't know what that is. So you have to be careful with these things. It doesn't understand the alias. And the reason why is that it's going to do the filtering and the where clause before it generates the output. So internally, the algorithm when it's trying to figure out the answer to your question is going to be looking through the tables. And the name of the column that it's going to be looking at at that time is still name. Right. It's not referring it referring to it as department name. So it's going to be a little careful using these aliases. But sometimes it works. So we'll see that. And it's another example. So this is our first example of a join. And in this case, we did the join using the where clause. But that's not the only way that we can do it. Indeed, there is what in my mind is a better way. And that is to use the join on syntax rather than doing the join in the where clause will do it in the from clause. And I like this approach better because it frees up the where clause just to do filtering. Right. So in this case, you can see that the new thing is we're doing the join here in the from clause. So we say the first table employee, which I'm referring to with the letter E. And then the syntax inner join statement inner join followed by the next table that we want to join to that first table, which is the department table. And then we use the keyword on. And then we specify how it should link those two tables together in this case by matching values of department ID. Now, again, what I like about this syntax is it allows us to do the join in the from clause rather than in the where. And as we'll see if you learn this pattern, this same pattern here can be used to do all of the outer joins as well as an inner join. To whereas the approach where we saw the where clause cannot be used to do outer joins as efficiently. So my recommendation is that you learn this version of it is doubly beneficial because it again it frees up the where to do just filtering. And once you learn this. Approach inner join with on. And then you can easily adapt that to do any of the outer joins that you may need to do, which we'll learn about here in a moment. Okay, so let's see an example of using this join on approach. Okay, so we'll do exactly the same thing. Except here. So we move it up to the from here we're going to do an inner join. And then we have to specify how to link those tables together, which we'll do so. In this case I don't even have a where clause, right, it's just a select that a from. And we're doing the join here in the from clause. So if we run this we'll get the same results that we got before using the where approach. But in this case we've freed up our where clause to do just basic normal filtering. So for example, if we wanted to limit the results to I don't know employees that work in the capital. And then we could use the where clause just for that. And we would get our results filtered appropriately. And so again, I do recommend that you learn this join on syntax to do all of your joins because it can be used with all types of joins, not just the inner joins.