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. And 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 and we're doing an inner join on the employee salary. Now we have to tell MySQL exactly what columns we're supposed to be joining on. So, 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_ID is equal to, and then in the salary table, it's also the employee ID. So, let's do employee, if you spell it right, employee_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 in 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.employee ID, and then employee salary.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, that's 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 of 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'll 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 so 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, very quickly, this is honestly giving me some anxiety because this is so incredibly long. Something that I mentioned in the beginner 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. We 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.employee ID and SAL.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 want to select the employee ID, and we want to select age, and then we want to select their occupation. If we try to run this, we're going to get an error, and 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.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. All right, 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 select everything, because I think it'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 for a 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, and I want to make it all caps, make it all the same. Uh, 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, or 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, and let's do this from employee_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 MySQL. 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 are 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 EMP1, and I'm going to say EMP2, 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 EMP1.employee. And just so you know, if it populates like this, you can hit tab and it'll auto finish that for you. And EMP2.employee_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 will be their secret Santa. So, 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 2.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 ID of two. Ron Swanson is going to be assigned to Tom Haverford, 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 EMP1.employee_ID. And we can call this, we'll just say as EMP_Santa. Then 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 Nope is going to be the secret Santa for Ron Swanson. I don't know if I made that clear, but that's how it works. So, now we need to do EMP1. and we'll do first_name, and we'll do as, do as, first_name_Santa. And we can do a comma, we'll do the exact same thing except for the last name. So, last_name as last_name Santa. Now, all we have to do is do a copy all this, bring it down here, and change this to two. So, we're pulling from the second table. And it will 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, then 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 is 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, last thing that I want to show you is how we can join multiple tables together. So, we're going to say joining multiple, can you 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. Now, 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 and we're going to say select everything. We'll do this from park_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 inner 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. And then we're going to say DEP and we'll say department ID is equal to the PD. 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 another 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, Healthcare, 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.
