 Hello, everybody. In this lesson, we're going to be taking a look at joins. Joins allow you to combine two tables or more together if they have a common column. That doesn't mean the column name has to be the exact same, but at least the data within it are similar that you can use. There are several joins that we're going to look at today, like inner joins, outer joins, and self joins. These are the two tables that we'll be working with the most throughout this lesson. We have the employee demographics table, as well as the employee salary table. Now, within the employee demographics table, we do have this employee ID column. If we look at the employee salary, we also have the employee ID column. So in this instance, the column name is actually the exact same. And of course, the data inside of it is also very similar. So let's start by writing out an inner join. This is probably one of the most common joins, one of the most simple joins as well. An inner join is going to return rows that are the same in both columns from both tables. So let's see how we can actually write out this join. Let's come right down here. And let's copy this. This will be the first table that we start with. And then we'll join the salary table onto this demographics table. So what we need to do is we need to come right here, and we need to say join. Now by default, join represents an inner join, although we can write inner join here to make it more explicit explicitly writing out inner join. Then we're going to come up here, and we're going to say employee salary. So we're selecting everything from the employee demographics, we're doing an inner join on the employee salary. Now we have to tell my sequel exactly what columns we're supposed to be joining on. I'm going to hit enter, and I'm going to hit tab. Now you don't have to hit tab. It just looks more code like and it's easier to read. And that's how I've done it for other programming languages as well. So that's how I'm going to show you how to do it. What we need to do is say on now this keyword is going to allow us to say we're joining the demographics table to the salary table based on these two columns. So from the demographics table, we're doing the employee underscore ID is equal to. And then in the salary table, it's also the employee ID. Let's do employee that you spell it right employee underscore ID. Now if we try to run this and let's do this, we're going to get an error. And let's bring this up. It's going to say column employee ID on the n clause is ambiguous. Now what does it mean ambiguous? That means that it doesn't know what table this employee ID is from. Is it from the employee demographics table? Is it from the employee salary table? We don't know because it's ambiguous. Now what we can do is we can specify it by saying employee demographics dot employee ID and then employee salary dot employee ID. Now if we run this, we're going to get the output that we're looking for. And let's take a look at this real quick. Let me bring this up. So we're pulling everything from the employee demographics right here all the way through the birth date. Then we're pulling the employee salary table. That's the employee ID all the way. Let's scroll over through the department ID. So we're basically pulling in all of the rows or all the columns from both tables, but we're not pulling in all of the rows. Remember an inner join is only going to bring over the rows that have the same values in both columns that we're tying on. So in this employee ID, we're missing number two. Are we missing any other ones? No, we're only missing number two. Let's go back up and I'm going to run both of these tables and we're going to take a look. So let's run this. So you'll notice in the employee salary table, we have a number two right here. And that's Ron Swanson. But in the employee demographics table, we don't have that. I believe that Ron Swanson did this that Leslie Nope would not know when his birthday was because he didn't want to bring that information. I think that makes the most sense, although Ron was not willing to give a comment on that. Now, if we run this again, you'll notice that two is not in there. Since two is not in the employee demographics table, the employee ID two is not going to be populated or brought over into this output from the employee salary table. Now really quickly, this is honestly giving me some anxiety because this is so incredibly long. Something that I mentioned in the beginner's series is that you can use something called aliasing when using joins and it's really helpful. This is what I mean. So right here, we have employee demographics. We're going to call this DEM. You can also do as DEM. You can do as SAL. These are just short names for demographics and short name for salary. And we can replace these and say DEM dot employee ID and SAL dot employee ID. Oh, that looks so much better. Now we're going to run this. And it'll be the exact same output. But now we're using these aliases, which just makes it so much easier to read. Now one last thing that I want to show you while we're just looking at the inner join is selecting the actual columns. Let's say we wanted to select the employee ID. And we wanted to select age. And then we wanted to select their occupation. If we try to run this, we're going to get an error. It's going to be almost the exact same error that we got before, which is column employee ID in field list is ambiguous. So in our field list, which is right up here in the select statement, we have this employee ID. It does not know which employee ID to pull from, whether it's the demographics or the salary. So we have to tell it, which one to pull from. So let's pull it from the demographics by saying DEM dot employee ID. Now when we run this, we're able to get information from both tables in our output without having all of the information. And if there are columns that are similar in both tables, we have to denote that by using this alias or the table name. Alright, so that is inner joins. Now, let's move down here. I'm going to copy this and we're going to come right down here. And we're going to look at outer joins next. And let's put that right here. Now for outer joins, we have a left join, and we have a right join or a left outer and a right outer join. A left join is going to take everything from the left table, even if there's no match in the join. And then it will only return the matches from the right table. The exact opposite is true for a right join. Let's see how this actually works. Let's start by changing this to a left join or a left outer join. They're both the same. And you can use them. Similarly, I'm just going to say left join. And we're joining it on the exact same things. And I'm going to take everything. So I think that'll be easier to visualize. And I'm going to run this. Now you may notice that this looks exactly the same. And that's very very good reason. It's because in the left table, which is the employee demographics table, the from statement, that's our left table. And then the join where we're actually joining on, that's our right table. So this is our right table. So since we're doing a left, it's taking everything from the employee demographics. Now remember, the employee demographics didn't have Ron Swanson. It had no information. So everything in the right table had a match. Let's change this to a right join. And what this is going to do when I want to make it all cap, make it all the same. What this is going to do is it's going to take everything from the employee salary table. But if there is not a match in the employee demographics, it just will have nulls. Let's go ahead and run this. So now it looks a little bit different. Now we're taking everything from the employee salary. So we're taking Ron Swanson. But if there is not a match, it will still populate that row, but it'll have all nulls in it. Then any of the information that is overlapping are the same, it will bring over. So employee ID is matched to employee ID one, then we'll bring all that information over from the left table. And that's essentially what a left and a right join is with a left table, you're taking everything from the left table and then matches from the right table. If you do a right join, you're taking everything from the right table, but only matches on the left table. And again, it populates it with nulls. Now let's go down and look at our last type of join that we're going to look at. And this is a self, let me spell that right, a self join. Now what is a self join? It is a join where you tie the table to itself. Now why would you want to do this? Let's take a look at a very serious use case. Let's do select everything. Let's do this from employee underscore salary. And let's run this. Now let's say it's December 1st, and the employee and Rex department decided to do a secret Santa. And they wanted to assign based off of their employee ID, the person who they're going to have as a secret Santa. We can help orchestrate this very easily using my SQL. Well, very easily is subjective, I guess, but let's take a look at how we can do this. So just like any other join, the first thing we're going to do is select everything from employee salary and then say join. And then we're going to say employee salary again, so we're tying it to itself. Now when we come down here, and let me do this, when we come down here and we say on, we have to specify which table we're pulling from. Are we pulling from the left table, which is like the first table we're pulling from or we pulling from when we're joining on the right table, we need to be able to distinguish these two tables because they are the same. So I'm going to say EMP one, and I'm going to say EMP two, just to say this is employee table one and employee table two. So we're going to tie them based off the employee ID, because we know those will be the exact same because we're pulling from the same table. So we'll do EMP one dot employee. And just so you know, if it populates like this, you can hit tab, and it'll auto finish that for you and EMP two dot employee underscore ID. Now if we run this, let's do this, the output that we're going to get is literally just a one for one match. It's all the columns and all the rows because they all match exactly. But now what we're going to do is we're going to assign an employee ID to the next employee ID and that'll be their secret Santa. So we just keep it really simple, the next highest person with an employee ID, that is their secret Santa. So let's do an employee ID plus one is equal to employee two dot employee ID. So we're adding one over here, and we're saying that's equal to this employee ID over here. Let's run this. So now you can see Leslie Nope is now going to be assigned to Ron Swanson, who has an idea of two. Ron Swanson is going to be assigned to Tom Havenford, which I'm sure he's really happy about, and so on and so forth. Now let's bring this down here. And what we're going to do is try to simplify this and simplify this output a little bit because this is a little bit chaotic down here. So we're going to specify what columns we want in our output. What we're going to want is the employee ID, first name, last name, and then employee ID, first name, last name of the person who they got for secret Santa. So we're going to start with amp one dot employee, employee underscore ID. And we can call this we'll just say as amp underscore Santa. And we'll do a comma and we'll come down. Now I need to spell employee right. So we have our employee ID. And now we need our first name and last name. Now remember, Leslie note is going to be the secret Santa for Ron Swanson. I don't know if I made that clear, but that's how it gets how it works. So now we need to do amp one dots, and we'll do first underscore name. And we'll do as we do as first underscore name underscore Santa. We can do a comma. We'll do the exact same thing, except for the last name. So last underscore name as last underscore name Santa. Now all we have to do is do a copy all this, bring it down here and change this to two. They're pulling from the second table. And it'll look just like this and get rid of this comma. And this is done. Let's run it. And let's bring this up. So we have employee Santa, first name Santa, Leslie, last name Santa. Nope, that employee Santa. And we actually need to change these names. That is one thing we need to do. We'll just change it to employee name, first name, employee and last name, employee. And now when we run this, we have our Santa. And then we just have the employee who this person is going to be the Santa for. Now this kind of a silly way to look at it. But in essence, this is exactly how a self join works. Now the very, very, very last thing, I promise you, the last thing that I want to show you is how we can join multiple tables together. So we're going to say joining multiple, they spell it right, multiple tables together. Now not just one table together to another table, I'm talking about one table to another table to another table. So let's go all the way back up. We're going to take this right here and bring it all the way down. And what we're now going to do is we're going to tie in this table right here, the parks department. Let's actually look at this table and let's select everything real quick. And let's run this. Now let's go down here. I'm going to say select everything. We'll do this from park underscore departments. And let's run this. Now this is something called a reference table. This is not a table that most likely you'll ever add a bunch of information to. It's there to reference that we have these department names tables like the salary table or employee demographics table are going to change pretty often as people get raises or as they get older with their age. Those are going to be updated fairly often, whereas this parks department table is just there for reference. Now if we look down here in the columns, we have a department ID, then we have a department name. So we have the ID and the name of that ID. If we run our join and we scroll all the way to the right, you'll notice we have a DEPT ID. This stands for department ID. That's in the salary table. So what we want to do is join this department ID to the department ID from the parks and rec. So what we can do is we're going to say enter, join. And now we're going to join, let's scroll down just a hair. There we go. Now we're going to take this and do it off this. So we're going to call this PD for short. And we're going to say we're joining it on. Now we cannot join this parks department to the employee demographics table. Why is that? Well, the employee demographics table only has employee ID all the way through birth date. There's no common column that we can tie to this parks department. The only table that has a common column is this department ID in the salary table. So what we need to do is actually take sal. So we'll say sal dot, then we're going to say DEP. And we'll say department ID is equal to the PD dot. And we need to take the department ID. Now notice these are not the exact same name. They are a little bit different, but they have the same values. One thing I forgot to mention is that in this parks department, there's no repeating. That's why it's a reference. Whereas in the salary, the department ID repeats several times because multiple people are in the same department. So this reference table also usually does not have duplicates. Just one other thing to note, but we have now tied it successfully. Let's try to run this. And if we come down here, go all the way to the right, we now have the department ID 1111111 and the department ID and department name, Parks and Recreation, Health Care, Public Works, Finance, Public Works and Parks and Recreation. So this worked perfectly. So this is how you can tie multiple tables together if you have common columns between them. Even though employee demographics has no column that's related to the parks department table, we can still tie them together based through this employee salary because employee demographics can tie to employee salary and employee salary can tie to the parks department. And that really is the majority of what you need to know in order to use joins well. Now in the next lesson, we're going to be taking a look at something called a union.