Hey guys, in this video, we will take a look at left joins, right joins and cross joins. Now I already took the liberty of writing the three queries, one for the left join one for the right join and one for the cross join. And we'll just take the time to go through each one to see exactly what is happening. Now having just looked at the inner join, we can establish that the inner join will make sure that records on either side meet the condition that is stipulated. In the previous example. We looked at bringing back all of the enrollments and the classes and lectures Yes, the classes, the lectures and the courses and all our Inner Joins.

And as a result, if there was any class that failed to meet this condition where the lecture ID should be present on either side, then that record did not come back because we know that we have six courses or six classes rather sorry. Yes, we have six classes. And we only brought about three records when we did it. Inner Join query and that was because the there was no lecture in the lecture stable with a null ID. So these three records feel that condition. Now what if we were faced with a situation where we wanted to see all the classes, and if there were lectures, then we wanted to see the details.

If there were none, we still wanted to see all of the classes that were available. So a left join allows us to bring back all the records in the left table and any matching records in there on the right table. If there are any records. If not, then it will fill that role with null values. And when I see all the records on the left table, do note that this is the same syntax as an inner join, we have the select statement. We have the left instead of inner and then we have the keyword join.

And then we have the table and the condition just like the inner join. However, what this is doing is seeing that give me all the records on the table to the left of the giant statements. And here's our joint statement. So that means classes would be to the left of our joint statement, if I were to write it in one line, then you would see better if we look to the left we have classes. So bring back all the classes and if there are any lectures that meet this condition, then bring about the details also. Otherwise, still bringing about the classes.

And if we take a look at the execution of that query, then we would see what we mean. So here we see that in our in our results set, we have all of our courses, all of our classes sorry with all of the details and of course he could go ahead and join more details on to the courses and so on we already looked at doing that so you could just left join our inner join since no class should ever exist without a course ID so we know that no of course should ever fear that condition. But then where there is no lecturer, there are no lecturer details to display. However, we're getting back all six records that could possibly be brought back because we're bringing back all six classes that are available. So that is essentially how the left join works. It says whatever it is that the main table is or the table that is in the select statement is or is to the left of my statement of my join statement, bring back everything that is possible and try and match as many records based on the condition.

If there are no matches, then just put no and then we can move on to the right join which basically does the opposite. It says bring back everything from the right side of my SELECT statement are off my joint statements. And if there anything, any records matching in the table to the left, then bring them back, if possible. So if we take a quick look at the results of this, we're bringing back all the lecturers and if there are any classes that are assigned to a lecture or any classes Eric why's that these two tables have in common based on this condition that the lecture ID is present on either side, then bring it back, but I want to see all the lectures. And then this square would come in handy if you want to see all the lectures, and if they have any classes assigned to them.

And we look at that results that, we see that we're bringing back only three off the course of the classes that have lectures. And then we're seeing all of the other lectures with no class details attached to them. But we're still seeing all the lectures. So we can see here who is not assigned to a class at all. So left join and right join pretty much do the same thing. They just changed the direction of the quote unquote, main table.

And once again, that mean table is depicted by whichever table is to the left. In the case of a left join, whichever it is to the left of that joint statement. And in the case of a right join, just put this in one line also, whichever table is to the right of this joint statements. No more on from that, we take a quick look at the cross join. And the cross join pretty much just says whatever tables I'm joining, give me back as many records as possible. So in other words, you would get back the number of records in this table multiplied by the number of records in that table.

And that would pretty much be everything just join. I've never really had a chance or reason to use this but if we can just look at the results and see here that compared to the other joint statements, these this record set is really just odd. I've never really used the cross join before, but it's worth knowing. And I'm sure you can probably find use for it. But for know the most important ones at least the master and have functional use for would be your left your right under your inner and inner ear cases, you may need to join a table on itself and others Join is pretty much accomplished through an inner join. So there are situations where, let's say an employee table where all the employees of a company are stored.

But then some are supervisors, some are employees, and some are managers. And you wanted to see who reports to who. So you would have an employee ID, but then you also have the employee ID of the manager for that employee. So you would want a report that shows this employee reports to another employee. And so you would have the same ID and that primary key employee ID and sort of pseudo foreign key employee ID column. And then you could just inner join that same employee table on itself.

And then you could use an inner join and do that. But then the two tables would be the same table, just give them two different aliases, but basically that's a self join and that concludes all the types of joints that are available to you in my SQL, Happy hunting and remember, it's a practice