Hey everyone, welcome back. In this video, part 20 of topic 3 in our database class, I'm going to show you how you can use the three different kinds of outer joins in the structured query language. Let's get started. Let's now take a look at a left outer join. Now you'll see here that the syntax is very, very similar to what we just saw with an inner join, and we're going to have another join, except that instead of the words, inner join, we have left outer join here. Let me still have our on. And we're still matching them together. In this case, we're pulling data from our employee table and our department table and we're linking them together based on matching values of department ID. So what is a left outer join then? So what I want you to notice is that you have this join keyword. When you use this approach and more broadly left outer join. And on each side of that are two table names. So here we have employee. And over here we have department. Now when we say left outer join. We're referring to this table. This is the left table because it appears on the left side of our join state. So the left table, right table, left table, right table. It's just where is the table name relative to where the join keyword appears. If it's on the left side, the left table is that if the table appears on the left side of the right table, it appears on the left side, it's the left table. If the table appears on the right side, it's the right table. Now with that concept in mind, what is going on here and how is this different? How are these outer joins different from an inner join? Well, when you do an outer join, what you're telling the database, now let's talk about it as a left outer join. So when you're doing a left outer join, what you're telling the database is I want all of the rows. Out of the left table, and I want rows out of the right table where a match exists. So the possibility is there that we, let's say, for example, that we have employees who do not work or have not been assigned to a particular department. With an inner join, those employees would not be included in the results. With a left outer join, assuming the employee table is on the left, then any employees that are not assigned to a department would appear in the results. So as usual, the best way to understand this is to see it. So let's see an example. Now first, we need to take a look at the data in our employee table. So let's take a look at our employee table and I'll just open it up using the editor here, since we're already familiar with this. There's a quick way of glancing at the data. Now, if we look out here, we can see that currently, every employee is assigned to a department. But let's change that. Okay. Let's say that Joanna is not assigned to a department. Okay. So her department ID. Oops. Make sure I'm allowing nulls for that. Sorry, I'm going to go back into the design view. This is where that null status comes in. Let's see. So I don't have here. So department ID, I should allow null values for that. Okay. That's good. Then the other issue is this table is connected to my employee skill table. So that's why we were getting that other error. What was Joanna's ID? Now, you know what? We'll just create a new employee that way. We won't have to worry about this. So I'll just add a new employee out here. Let's say this is our newly hired employee. This will be employee number 13. And maybe we hire Dr. Cooper to color work for our company. Okay. Now note that Dr. Cooper is newly hired and is not yet assigned to a department. Therefore department ID is null. Now, let's run this example that we had before. We're asking for employee first name, last names and the department in which they work. And we're doing an inner join. Now we know that our newly hired employee Sheldon Cooper does not have a department assignment. It does not have a department ID. So if I run this, we take a look at these results. You will see that Sheldon Cooper, our newly hired employee, does not appear in the results. And this is because we're doing an inner join. When doing an inner join, the only results that the only rows that will appear in the results are those where a match exists between the two tables. So in this case, our match is a department ID in the employee table must match an existing department ID in the department table. If a match does not exist, then whatever employee we're currently considering will not be included in our list of results. And that is precisely why Dr. Cooper, it does not appear here in our results. However, if we did a left outer join, remember the employee table here is on the left side of the join keyword. So that is our left table. So if I did a left outer join, this says basically give me all of the employees. And if an employee is assigned to a department, then I want the department name in which that employee works. And so if I were to run this, you'll see that we now get Dr. Cooper included in our results. Because we asked for all of the employees regardless of whether they had a department assignment. And you can see his department name is listed as null. So in a left outer join, we get everything on the left table. And any matching values in the right table are also included in the results. If not, we'll just get a null value in our results set as we see here for Dr. Cooper's department name. So that's broadly the difference between an inner join and an outer join. Now, I imagine that it would be pretty easy for you to figure out what a right join does. A right outer join is a very similar concept. It just says give me all of the departments. And if they have matching employees include those as well. But I want all of the departments listed in the results, even if no employees work in that department. So it's the right table, the table on the right side of the join. That we're telling it include all the results out of that table, regardless of whether a match exists. Now, taking a look at our departments, let's just go here and. Create a new department really quickly. So does this load a little slow right there about that. So let's make a department number five. Yeah, let's make a department number 500 just to be silly. And I don't know, we'll call this marketing. Okay. So now we have a marketing department. But as we know, we don't have any employees who work in that department because we just created it. Right. So no employee is assigned to department ID 500. And of course, if we did our inner join and ran this, we took a look at our results. We do not see Dr. Cooper in the results. And we do not see the marketing department in the results. Right. Because Dr. Cooper is not assigned to a department. And the marketing department currently has no employees. So they are excluded from the results since we're doing an inner join. Only results with matching values and both tables will be included. And we saw when we did a left outer join. That we get everything out of the left table, which in this case is the employee table. So we'll see Dr. Cooper listed here with no department. And I'm sure you can imagine what happens if you do a right join. So a right join in this case, the department table. We run that. And you'll see that we get the marketing department included in the results with no employee name. But you'll notice that Sheldon Cooper does not appear in this list. Because we're currently telling the database, give me everything out of the department table. And if there's a match based on matching values of department ID in the employee table, then give me that employee's name as well. Okay. So we know that this will exclude our employee that does not have a department. That is, Dr. Cooper won't be included. So we get everything out of the right table with the right outer join. We get everything out of the left table with a left outer join. And what do you imagine happens if we do a full outer join? With a full join, you're going to get all of the records out of the left table and the right table. And where a match exists, that match will be shown in the results. Otherwise, we'll just see null values. Okay. So with a full join, I'm going to see in this particular example, Dr. Cooper will be included in the results. And the marketing department will also be included in the results. So I run this and we can see that's here we have Dr. Cooper, even though he is not assigned to a department. And here we have the marketing department, even though it does not have any employees who work there.