WEBVTT 00:07.820 --> 00:08.120 I will. 00:08.120 --> 00:10.670 Coming back again to the lecture and MySQL. 00:10.670 --> 00:15.170 And in this lecture we go ahead and look at uh joints. 00:15.170 --> 00:20.480 So we're going to look at uh inner join right join and left join. 00:21.140 --> 00:23.120 All right let's go ahead and see. 00:23.120 --> 00:26.480 In the previous lecture we were able to create the foreign key. 00:26.510 --> 00:31.250 And we're able to link up the teacher table with the subject table. 00:31.250 --> 00:36.920 And uh, when we try to delete some things, we can see we are not able to delete that. 00:36.920 --> 00:42.260 And most of us might be like, we need to see more to actually understand what we are doing. 00:42.260 --> 00:45.290 So we are going to use join to actually do that. 00:45.290 --> 00:52.550 So join is a class that we use to combine rows from two or more tables based on their related columns 00:52.550 --> 00:55.370 between them, such as the foreign keys. 00:55.670 --> 01:02.140 So using join lets us to check uh, partition table and a subject table and what they have in having 01:02.140 --> 01:02.680 come on. 01:02.680 --> 01:07.030 So we're going to check the inner join the right join and the left join. 01:07.060 --> 01:17.350 Now let's go ahead and say I want to select all from a subject. 01:18.100 --> 01:19.750 So let's select all from subjects. 01:19.750 --> 01:24.700 And I want to be able to get whatever is in common between the two. 01:24.730 --> 01:31.540 Because when we assign this to assign to the channel with ID two, one, three and two, again we need 01:31.540 --> 01:33.790 to assign anything to teacher with ID four. 01:33.820 --> 01:35.890 Remember we have a teacher with ID four. 01:35.920 --> 01:36.490 All right. 01:36.490 --> 01:40.660 So we want to also see if this actually works or not. 01:40.660 --> 01:42.640 So select all from subjects. 01:42.640 --> 01:45.010 And then we're going to use the inner join first. 01:45.040 --> 01:50.020 The inner join will give us whatever it is in common between these two teams okay. 01:50.050 --> 01:53.380 So let's go ahead and say uh inner join. 01:54.370 --> 01:57.280 And then we're going to say inner join. 01:57.280 --> 01:58.840 And the table you want to join. 01:58.840 --> 02:00.850 We're going to check out the table. 02:00.850 --> 02:03.010 So subject and teacher. 02:03.010 --> 02:06.020 So the teacher's table is what we are going to join. 02:06.020 --> 02:14.000 And now we're going to join dots on say subjects. 02:15.980 --> 02:25.970 Subjects dot the column that you have in common and that is teachers ID is going to be equal to teachers 02:26.000 --> 02:28.340 ID the column they have in common. 02:28.550 --> 02:29.570 Dots. 02:31.730 --> 02:35.360 Sorry teachers teachers table that we currently have in common. 02:35.360 --> 02:39.470 So that is the teachers ID with a semicolon at the end of this. 02:39.470 --> 02:42.800 And now let's go ahead and execute this query. 02:42.800 --> 02:45.650 And you can see we have them here. 02:45.650 --> 02:49.760 And we have subject ID 101 102 103 104. 02:49.760 --> 02:53.420 And the subject name is physics, chemistry, biology and history. 02:53.420 --> 02:58.010 And the teachers ID that were assigned is two, one, three and two. 02:58.130 --> 03:02.210 And now we have teachers ID two, one, three and two. 03:02.240 --> 03:09.520 And the first name is Steve, George and Steve, you can say that it was arranged like a judge. 03:09.520 --> 03:12.790 Steve M-1 global, just like we did previously. 03:12.790 --> 03:18.910 So you can see that it's much each of the teachers with a subject that they're assigned to. 03:18.940 --> 03:23.710 So as you assign each subject to a teacher, you can see that each of those subjects you are sent to 03:23.740 --> 03:24.370 a teacher. 03:24.370 --> 03:27.160 And that is really very, very interesting. 03:27.220 --> 03:29.110 So I hope that is really very interesting. 03:29.110 --> 03:30.250 So check it out. 03:30.280 --> 03:33.700 Now let me go ahead and say I want to just remove the teachers ID. 03:33.880 --> 03:38.410 I just want to have only a subject name and the first name of the teacher. 03:38.410 --> 03:42.220 So when you come let me say you are extended as a database manager. 03:42.250 --> 03:46.090 I just want to have only the physics and the teacher name. 03:46.090 --> 03:49.210 So whenever you come you say, oh, Steve is assigned to physics. 03:49.480 --> 03:51.820 You don't need to teach your ID to be shown. 03:51.820 --> 03:53.500 How can you be able to do that? 03:53.830 --> 03:55.810 So that is very simple. 03:55.840 --> 04:00.250 Go ahead and say instead of selecting all, you go ahead and say select. 04:00.250 --> 04:10.140 And then you put in, let me say the subject uh underscore name and the first name. 04:11.640 --> 04:15.180 So that is the first underscore name. 04:15.300 --> 04:15.900 All right. 04:15.900 --> 04:17.910 So we selected everything. 04:17.910 --> 04:19.260 We just select these. 04:19.260 --> 04:22.290 And then let's go ahead and execute this query. 04:22.290 --> 04:31.740 And uh it says unknown is subject and not some name or a subject and not some name. 04:31.770 --> 04:32.250 All right. 04:32.280 --> 04:34.290 Now let's check it out. 04:34.290 --> 04:39.090 So we have the subject name physics assigned to first name teacher Steve. 04:39.090 --> 04:41.820 And chemistry is assigned to judge. 04:41.820 --> 04:47.460 And biologists are sent to memoir and the history is assigned to Steve. 04:48.060 --> 04:49.980 Well, that is really interesting. 04:50.010 --> 04:51.870 So you can sing a communication with two tables. 04:51.870 --> 04:57.840 So using the drawings we are able to actually get these two tables combined together. 04:58.680 --> 05:00.150 I hope that is really very interesting. 05:00.180 --> 05:03.390 Let's go ahead and say I want to add in the subject code. 05:03.390 --> 05:08.550 So it's going to be physics 101 is assigned to uh Steve. 05:08.570 --> 05:15.920 So let's go ahead and maybe you can put this to start before the first name or after the start before 05:16.040 --> 05:19.730 going to say subject's ID. 05:21.740 --> 05:24.560 So I actually went to right subject ID. 05:27.200 --> 05:30.320 So after subject ID we'll go ahead and put in a subject name. 05:30.320 --> 05:36.110 So I'll say subject underscore name name put a comma. 05:36.110 --> 05:38.930 So we'll have subject ID subject name and the first name. 05:38.960 --> 05:40.340 And go ahead and execute this. 05:40.340 --> 05:48.830 So the subject id id is 101 assigned to the task Steve Kevin 102 assigned to teacher George and our 05:50.150 --> 05:56.900 biology 103 assigned to teacher and Memoir and History 104 assigned to teacher. 05:57.770 --> 05:58.430 Steve. 05:58.970 --> 06:00.650 Wow, that is really very cool. 06:00.830 --> 06:07.820 So now what if what if I want to show what the teacher that we are not assigned. 06:08.720 --> 06:10.180 So how can we want to do that. 06:10.210 --> 06:12.580 So why had I changed this from inner join? 06:12.580 --> 06:15.610 Unless change changed to right? 06:16.180 --> 06:16.720 No. 06:16.720 --> 06:21.760 That is go over here and say this is inner join. 06:21.760 --> 06:22.960 Go ahead and remove this. 06:22.960 --> 06:25.360 And I go ahead and say right join. 06:25.360 --> 06:27.190 So this is going to populate everything. 06:27.220 --> 06:28.780 Go ahead and run this and check it out. 06:28.780 --> 06:30.910 So we have everything. 06:31.360 --> 06:32.830 We have everything. 06:32.830 --> 06:39.610 And in this way it was arranged because we start from George Steve and Steve again and memoir and global. 06:39.610 --> 06:46.240 And now you can see under subject id id one one no cost was assigned. 06:46.660 --> 06:47.680 You see that. 06:48.340 --> 06:53.050 And I have a no no no for global. 06:53.050 --> 06:55.810 So global has nothing assigned to him or her. 06:55.840 --> 06:58.330 And you can see that no subject and nothing. 06:58.330 --> 06:59.950 And I want to populate everything. 06:59.950 --> 07:06.460 You remove all this and put exteriors and run this again and you have everything shown. 07:06.640 --> 07:09.070 So we do not assign anything to global. 07:09.400 --> 07:12.440 And this arrange did showing everything. 07:12.890 --> 07:16.550 Now you can also change this from right to left. 07:16.580 --> 07:18.620 What's right will give you everything. 07:18.620 --> 07:20.060 So you can also have left. 07:20.090 --> 07:22.130 Join and run this. 07:22.130 --> 07:29.150 So the left join and inner join almost does exactly the same thing with the right join will actually 07:29.150 --> 07:30.560 populate everything. 07:30.560 --> 07:34.700 What people are saying a subject and people will not assign a subject. 07:34.700 --> 07:36.350 So go ahead and practice with it. 07:36.380 --> 07:37.820 I hope that is very, very interesting. 07:37.820 --> 07:43.040 So you can now see that the foreign key works and is actually complicated between these two taboos. 07:43.040 --> 07:44.900 And it put everything they have in common. 07:44.900 --> 07:45.950 You can actually see that. 07:45.950 --> 07:48.290 And that is with the help of the foreign key. 07:48.320 --> 07:50.900 That is when the join actually works. 07:50.900 --> 07:53.900 So pull it out, check it out and practice with it. 07:53.930 --> 07:57.410 If you have any question, go ahead and use the question and answer section. 07:57.410 --> 07:59.750 And I'm going to get back to you as soon as possible. 07:59.780 --> 08:00.650 Thank you so much. 08:00.650 --> 08:03.680 And I'm going to see you in the next video lecture.