Hey guys, in this video, we will be picking up where we left off last time about inner joints. And we'll be looking at other types of joins that we can do between tables, namely left joins and right joins. Now before I proceed, I just want us to review some of the design decisions that we made on our enrollments table. And those would be surrounding the necessity of values for the foreign key values. You'll recall that we allowed notes on each of our foreign keys and of course, those design decisions can be changed, because an enrollment really shouldn't occur if a student and of course don't go together. However, in practical terms, there can be an enrollment of students in a course, but no teacher being assigned to that enrollment just yet.

And so in keeping with that design decision, I actually added a few more rows a few more rows of data to our Courses table, teacher table and the enrollments table. So here you see that I selected all from enrollments and I have a few columns down to the end where the teacher ID is actually no. But we have students being assigned to course with ID seven, just no lecture being assigned to that course just yet. I have included scripts that will insert additional courses and additional teachers into the database so you can go ahead, I also made a design change, I changed teacher to teachers, that's the name of a table teacher to teachers as I lift up, so I made that part and so you can feel free to change that design or just modify the script according to what you have. Now let's take a look at the concept of left and right giants.

Now once again, let's take a look at our SELECT statement on our enrollment. Instead, which is bringing back every single enrollment that is currently in the system Well, at least the top 1000. Since we have fewer than 1000, then we're getting about all of them. And we see here that the between IDs 17 through 21, there would be no lecturer or teacher assigned to those enrollments. Now here I've written an inner join statement, which is going to bring back all the columns between the two tables, enrollments and teachers. That's what the star means, if you remember.

And the expectation is that the inner join is actually just going to bring back on either side where the condition is met, meaning the teacher ID in the enrollment stable and I'm using that aliases, EMT, II for enrollments, D for teachers. So he dot teacher ID most exactly much the ID in the teacher table. And if that condition is not met, then that rule will not be returned. And so the expectation is that When we execute this query the rows 17 through 21, or IDs 17 through 21. And enrollments will not be returned because there is no teacher in the teachers table with an ID of null. So no will not match the any of those values.

So I press f5. And here we see that both query results are here stucked. And in the top result, we see that we're bringing back 17 and a total of 17 rows for the enrollments. However, in the lower stock, we're seeing that we're only bringing back 12, which clearly means that there were admissions as we went along. So columns 17 through 21, or enrollments 17 through 21. Were not returned because they did not meet the minimum condition.

Now in a scenario where you want it to bring back every single enrollment, regardless of the presence of the data, too much this exact condition, that's where your left join comes in. So the left joins really easy. It's just a modification of a typical INNER JOIN where instead of seeing inner, you literally just say left. And what happens here is that it will bring back every single thing on the left side of the query. And I'm seeing the left side of the query, because if you read from left to right, and I'll just put this all in one line, then you see that the enrollments table is to the left of the giant. And the teacher said this to the right of the giant.

So the left join is saying bring back every single rule every single row of data from the table that's on the left. And if there is a much then you can bring back any matching data on the table and condition on the right. But even if there is no matching data, still bring back that column on the left. So when I execute this query, the expectation is that this will bring back every single enrollments, just that there should be a few more nodes in our results. So that's Execute and see what happens. All right, so I wrote an entirely different statement for our left joints.

Now we have three queries being run in this script alone, each one being displayed here in its own stack. So up top, we have Select star from enrollment, so we know that enrollments would have about five more rows. And below we have the inner join data set, which should only have about 12. So this one is only bringing back the exact matches. But then the last one, which would be the result set, from our left join, would actually have every single enrollments. Alright, so we do have enrollments and see we're bringing back IDs 17 through 21 with the enrollment details, but because there is a null value in the teacher ID, there is a no match because well, it didn't bring back the corresponding data from our teacher table.

So that's the power for left join. You may be faced with a situation where you know you need by Everything from one side, but then you need additional data if it's available. But the table, the main table is absolutely important. And that would be the situation the enrollments are absolutely important. Maybe the administration wants to see how many students have enrolled in that class, regardless of if they have a lecture or not. So this query would actually bring back every single enrollment that exists regardless of the presence of a teacher.

The reverse may also be true. So we're right now we're catering to the table on the left. And we could always swap this out, we could put teachers on the left side and the words and T and enrollments on the right side. And that would actually bring back every single teacher, regardless of if they are currently assigned to an enrollment or to a class. But instead of doing all of that, and that switching, we could actually just use our right join, which would basically do the reverse of the left Join, where it would bring back everything in the table to the right of the join. And regardless of if it has matching data in the table to the left, so here I've written a select statement with our right join instead.

So this time the bias is on the teachers table where you want to see every single teacher know I included some teachers, and certain some more teachers, not all of them have been assigned to an enrollment. So the expectation is that we're going to see every single teacher and an enrollment if it exists, and if it doesn't exist, then the enrollments would be blank. So once again, I have all of my queries here stuck to have my Select star from enrollments up top, I have my inner join here, I have my left join here. And then when I go down, I see that I have my right here. And if I scroll through, we see that the lecture details do repeat for each enrollment for which they are associated, right? So everywhere there's ID two in an enrollment, that's where john McDonald is.

However, we can see here that these three lectures that were just added, were not assigned to any class. So we bring back all of the lectures on the right. Alright, so remember, we're using right join. So the teacher still is to the right, so we're bringing back every single lecture. And if there's an enrollment and we see the details, if not, then everything is not. And you may note also, that the that the enrollments that we're not given a teacher ID, and I'll just go back quickly, these enrollments with IDs 17 through 21.

They're not being returned here because once again, the right join is giving the bias to the teacher to the teachers table, which is on the right, so the left join gives by us which of our tables on the left bringing back everyone single record regardless of if there is a match or not, and the right join basically does the opposite. So it brings back everything to the right regardless of if there is a much for the data that's on the left. The final statement I will show you is the full join, which basically says, I want everything on either side. If there's a much, then bring me back one rule with all the details. If there is no much, then bring me back from either side regardless, and that is what the full joint does. So we have the left, we have the right, and we have the full And let's not forget or enter.

So enter sifts through and make sure that there's an exact much left says if there's an exact match, then that's fine, but bring me everything to the left side of my query. Right join does the opposite of left join. What was right isn't that what was left isn't all right. And the full join says I don't care which direction you are. Just bring the buff and if there's a And then fine. And so the last results that we're looking at below actually depicts what happens during the full join.

So we see here that all of the roles that would have been missing in the inner join or left join, right join, they're all being brought back right here. So are lectures without an abiding city and our enrollments without the lecture, and all enrollments that have lectures assigned or teachers assigned, everything is going back and that's the power of the full join. So you can go ahead and play around with this. The three script files have been included in the resources for this video. Have fun