I will.
Coming back again to the lecture and MySQL.
And in this lecture we go ahead and look at uh joints.
So we're going to look at uh inner join right join and left join.
All right let's go ahead and see.
In the previous lecture we were able to create the foreign key.
And we're able to link up the teacher table with the subject table.
And uh, when we try to delete some things, we can see we are not able to delete that.
And most of us might be like, we need to see more to actually understand what we are doing.
So we are going to use join to actually do that.
So join is a class that we use to combine rows from two or more tables based on their related columns
between them, such as the foreign keys.
So using join lets us to check uh, partition table and a subject table and what they have in having
come on.
So we're going to check the inner join the right join and the left join.
Now let's go ahead and say I want to select all from a subject.
So let's select all from subjects.
And I want to be able to get whatever is in common between the two.
Because when we assign this to assign to the channel with ID two, one, three and two, again we need
to assign anything to teacher with ID four.
Remember we have a teacher with ID four.
All right.
So we want to also see if this actually works or not.
So select all from subjects.
And then we're going to use the inner join first.
The inner join will give us whatever it is in common between these two teams okay.
So let's go ahead and say uh inner join.
And then we're going to say inner join.
And the table you want to join.
We're going to check out the table.
So subject and teacher.
So the teacher's table is what we are going to join.
And now we're going to join dots on say subjects.
Subjects dot the column that you have in common and that is teachers ID is going to be equal to teachers
ID the column they have in common.
Dots.
Sorry teachers teachers table that we currently have in common.
So that is the teachers ID with a semicolon at the end of this.
And now let's go ahead and execute this query.
And you can see we have them here.
And we have subject ID 101 102 103 104.
And the subject name is physics, chemistry, biology and history.
And the teachers ID that were assigned is two, one, three and two.
And now we have teachers ID two, one, three and two.
And the first name is Steve, George and Steve, you can say that it was arranged like a judge.
Steve M-1 global, just like we did previously.
So you can see that it's much each of the teachers with a subject that they're assigned to.
So as you assign each subject to a teacher, you can see that each of those subjects you are sent to
a teacher.
And that is really very, very interesting.
So I hope that is really very interesting.
So check it out.
Now let me go ahead and say I want to just remove the teachers ID.
I just want to have only a subject name and the first name of the teacher.
So when you come let me say you are extended as a database manager.
I just want to have only the physics and the teacher name.
So whenever you come you say, oh, Steve is assigned to physics.
You don't need to teach your ID to be shown.
How can you be able to do that?
So that is very simple.
Go ahead and say instead of selecting all, you go ahead and say select.
And then you put in, let me say the subject uh underscore name and the first name.
So that is the first underscore name.
All right.
So we selected everything.
We just select these.
And then let's go ahead and execute this query.
And uh it says unknown is subject and not some name or a subject and not some name.
All right.
Now let's check it out.
So we have the subject name physics assigned to first name teacher Steve.
And chemistry is assigned to judge.
And biologists are sent to memoir and the history is assigned to Steve.
Well, that is really interesting.
So you can sing a communication with two tables.
So using the drawings we are able to actually get these two tables combined together.
I hope that is really very interesting.
Let's go ahead and say I want to add in the subject code.
So it's going to be physics 101 is assigned to uh Steve.
So let's go ahead and maybe you can put this to start before the first name or after the start before
going to say subject's ID.
So I actually went to right subject ID.
So after subject ID we'll go ahead and put in a subject name.
So I'll say subject underscore name name put a comma.
So we'll have subject ID subject name and the first name.
And go ahead and execute this.
So the subject id id is 101 assigned to the task Steve Kevin 102 assigned to teacher George and our
biology 103 assigned to teacher and Memoir and History 104 assigned to teacher.
Steve.
Wow, that is really very cool.
So now what if what if I want to show what the teacher that we are not assigned.
So how can we want to do that.
So why had I changed this from inner join?
Unless change changed to right?
No.
That is go over here and say this is inner join.
Go ahead and remove this.
And I go ahead and say right join.
So this is going to populate everything.
Go ahead and run this and check it out.
So we have everything.
We have everything.
And in this way it was arranged because we start from George Steve and Steve again and memoir and global.
And now you can see under subject id id one one no cost was assigned.
You see that.
And I have a no no no for global.
So global has nothing assigned to him or her.
And you can see that no subject and nothing.
And I want to populate everything.
You remove all this and put exteriors and run this again and you have everything shown.
So we do not assign anything to global.
And this arrange did showing everything.
Now you can also change this from right to left.
What's right will give you everything.
So you can also have left.
Join and run this.
So the left join and inner join almost does exactly the same thing with the right join will actually
populate everything.
What people are saying a subject and people will not assign a subject.
So go ahead and practice with it.
I hope that is very, very interesting.
So you can now see that the foreign key works and is actually complicated between these two taboos.
And it put everything they have in common.
You can actually see that.
And that is with the help of the foreign key.
That is when the join actually works.
So pull it out, check it out and practice with it.
If you have any question, go ahead and use the question and answer section.
And I'm going to get back to you as soon as possible.
Thank you so much.
And I'm going to see you in the next video lecture.