WEBVTT 00:07.220 --> 00:10.280 I will call you back again to another video lecture in MySQL. 00:10.280 --> 00:15.110 And in this video lecture, let's go ahead and learn about the union clause. 00:15.140 --> 00:22.640 So the union clause is used to combine the results of two or more Select statements in MySQL. 00:22.760 --> 00:24.860 So let's go ahead and say we have this table right. 00:25.160 --> 00:26.810 And this is our students. 00:26.840 --> 00:29.930 And remember we are dealing with my school database. 00:29.930 --> 00:35.840 So let me say the owner of the school jumped in and say hey, the database manager, I need you to provide 00:35.840 --> 00:42.800 me every name of teachers and their subject in two columns only. 00:43.250 --> 00:43.700 All right. 00:43.700 --> 00:44.870 Let's go ahead and check. 00:44.900 --> 00:49.880 First of all, go ahead and select, uh, the teachers table and the subjects table. 00:49.880 --> 00:50.570 Let's check it out. 00:50.570 --> 00:56.420 So select all from the teachers table and let's check it out. 00:56.420 --> 01:02.120 So this is a test table which you have x and it has only two columns. 01:02.150 --> 01:02.870 Good. 01:02.900 --> 01:08.040 Now let's check the subject's table and see what he looks like. 01:08.070 --> 01:09.300 Subject's. 01:09.360 --> 01:12.600 And the subject's table has two columns as well. 01:12.630 --> 01:13.290 Fine. 01:13.320 --> 01:13.920 That is good. 01:13.950 --> 01:17.490 Now we have two columns to deal with, so no problem at all. 01:17.520 --> 01:18.570 Then after. 01:18.570 --> 01:26.850 What we need to do now is we want to give the owner of the school these subjects and teachers in just 01:26.850 --> 01:28.170 two columns only. 01:28.170 --> 01:38.010 So what I need to do is go ahead and add union, and then I will also select uh oh from teachers, press 01:38.010 --> 01:38.970 semicolon at the end. 01:38.970 --> 01:41.580 And let's go ahead and run this code and check it out. 01:41.580 --> 01:43.350 So here is a query. 01:43.350 --> 01:44.700 And this is what we have. 01:44.700 --> 01:50.160 And you can see we have subject ID and subject name as the name of the columns. 01:50.160 --> 01:56.460 And because we selected subject first the subjects are on top and our teachers are below. 01:56.460 --> 02:05.880 So you can see teachers first the ID one George Steve M-1 global and the teachers ID 105 106 107 108 02:05.910 --> 02:07.750 because this has an Autoincrement. 02:07.750 --> 02:14.770 And we're able to insert some things into here and uh, physics, chemistry, biology, history. 02:14.770 --> 02:20.380 So if you followed me from the beginning of this course, you can see that when you did this, you were 02:20.380 --> 02:27.100 able to or you see teachers ID here making the subjects table to have three columns. 02:27.100 --> 02:32.620 Go ahead and drop the foreign key and also drop the teachers ID to have just two columns. 02:32.740 --> 02:33.670 That is what I did. 02:33.700 --> 02:35.770 Now I was able to have this. 02:35.800 --> 02:45.730 If I go ahead and change this to be teachers, and then I change this to be subjects, then the teacher 02:45.730 --> 02:48.250 should be on top and the subjects is going to be below. 02:48.250 --> 02:54.700 So teachers ID and the first, the first name that is the column names. 02:54.700 --> 03:00.550 And you can see here we have the teachers ID first, the teachers the first name here. 03:00.550 --> 03:03.640 And then we have the subjects below. 03:03.640 --> 03:08.440 So you can now print out this and give to the school and say, yes, I've been able to print this out 03:08.440 --> 03:09.460 and that is it. 03:09.970 --> 03:10.420 All right. 03:10.450 --> 03:18.430 Now know you can be able to add two tables using the union and then no problem. 03:18.460 --> 03:21.640 What if I have the teachers table? 03:22.240 --> 03:24.610 Let's say this is teachers table. 03:24.610 --> 03:30.580 And the other school comes back and says, okay, I need every teacher and every student in this in 03:30.580 --> 03:31.630 just two columns. 03:31.660 --> 03:35.830 Go ahead and say, let's check out the students. 03:36.460 --> 03:37.570 So these are the students. 03:38.230 --> 03:39.040 I check it out. 03:39.040 --> 03:45.400 And this is what we have about 123456. 03:45.400 --> 03:46.720 We have six columns. 03:46.720 --> 03:49.570 And this might give us problem. 03:49.570 --> 03:58.540 So let's go ahead and check it out and say union select all from teachers. 03:58.570 --> 04:01.600 And now let's go ahead and execute the query and check it out. 04:01.630 --> 04:02.740 Nothing happens. 04:02.770 --> 04:05.200 And I'm going to drag this so I can be able to see. 04:05.230 --> 04:09.100 And the error here says error code 1 to 2. 04:09.130 --> 04:15.920 The youths select statement have a different number of columns And that is the problem. 04:16.580 --> 04:17.840 Yes, that is a problem. 04:17.870 --> 04:19.250 So what actually happens? 04:19.250 --> 04:27.440 So under the teachers we can see that there are if you go ahead and select teachers only teachers first 04:27.440 --> 04:29.150 name and students have first name. 04:29.150 --> 04:30.500 So why not go ahead. 04:30.530 --> 04:36.020 Go back right in here and say remove all and select only the first name. 04:36.260 --> 04:40.280 All right select teachers ID and select the first name. 04:40.280 --> 04:44.960 So go ahead and say select A students. 04:44.990 --> 04:46.040 Go back over here. 04:46.130 --> 04:48.890 Let's go ahead and put this back again and check the students. 04:48.890 --> 04:52.850 And the student has students ID and first name. 04:52.850 --> 04:55.130 So select stress ID and first name. 04:55.130 --> 05:00.980 And then we go back right in here and check the teachers and teachers has teachers ID and the first 05:00.980 --> 05:01.400 name. 05:01.400 --> 05:06.740 Then go back right in here and remove the all from both of them. 05:06.740 --> 05:16.910 Now go back right in and say for the students I'm going to select a students underscore ID then comma 05:16.910 --> 05:22.020 and uh, I had um select underscore first name. 05:22.050 --> 05:31.140 Then for the teachers, I'll go ahead and select a teachers underscore ID and uh, the first name. 05:31.140 --> 05:33.150 Then let's go ahead and check it out. 05:33.180 --> 05:36.810 Well students ID because we select from students first. 05:36.810 --> 05:39.930 And this go ahead and drag this up and check it out. 05:39.930 --> 05:43.410 So we have Peter Oscar Paul rocks Mike. 05:44.040 --> 05:46.200 Uh Mike Queen Zendaya. 05:46.290 --> 05:52.380 Then the teachers you can see the teachers are listed judge Steve Steve and global. 05:52.380 --> 05:52.980 Wow. 05:53.010 --> 05:54.120 Hope that is cool. 05:54.420 --> 05:55.350 Oh well all right. 05:55.380 --> 05:56.130 Now that is it. 05:56.130 --> 05:57.030 Very simple. 05:57.030 --> 06:01.740 So you can be able to select the columns that are not of the same. 06:01.770 --> 06:05.640 Any columns that are not the same in nonviolent you can be able to select that. 06:05.640 --> 06:09.450 So you just go ahead and specify what you want to select. 06:09.870 --> 06:10.500 All right. 06:10.530 --> 06:17.100 Now what if this can actually print out only names that are unique. 06:17.130 --> 06:18.210 Yes. 06:18.360 --> 06:20.130 It can only print out names that are unique. 06:20.160 --> 06:22.150 Let's go ahead and remove everything right in here. 06:22.180 --> 06:29.080 And now go back and insert into teachers. 06:29.110 --> 06:30.730 Go ahead and insert into teachers. 06:30.730 --> 06:34.000 And I'm going to insert these values there. 06:35.200 --> 06:43.390 So inside this values I'm going to say for teachers I'll go ahead and insert Peter. 06:43.630 --> 06:49.840 And then I'll go ahead and select these so I can be able to see it. 06:53.470 --> 07:00.460 Select all from teachers and police. 07:00.460 --> 07:01.360 Semicolon and end. 07:01.390 --> 07:03.550 So let's go ahead and say maybe this is five. 07:03.550 --> 07:10.900 And then we go ahead and uh check out. 07:10.900 --> 07:12.820 So let's know what is the problem. 07:12.940 --> 07:15.100 So put semicolon at the end of this. 07:15.100 --> 07:16.240 So we have semicolon there. 07:16.240 --> 07:17.170 And that is it. 07:17.170 --> 07:19.180 Now run this code and check it out. 07:19.720 --> 07:22.840 Now we have another one called a pizza. 07:22.840 --> 07:25.330 And I can see it's starting to touch the walls. 07:25.330 --> 07:28.720 Five and now we have these. 07:28.750 --> 07:33.400 I want to go back to our code and we're just going to do this. 07:33.400 --> 07:34.750 So I moved back right in here. 07:34.750 --> 07:37.240 And I want to select students and teachers. 07:37.330 --> 07:38.770 And I did a union. 07:38.770 --> 07:41.680 Now let's go ahead and check if we printed out. 07:41.710 --> 07:43.390 Go ahead and check this out. 07:43.720 --> 07:48.340 Now we have pizza printed out here and here. 07:48.370 --> 07:51.940 Meaning pizza is already there. 07:52.480 --> 07:52.960 Okay. 07:52.990 --> 08:03.010 Now what if I remove this first name or remove the IDs and then this code hadn't executed query and 08:03.010 --> 08:05.140 I have pizza and a half. 08:05.170 --> 08:05.950 Go back here. 08:06.010 --> 08:07.840 I have pizza just once. 08:08.680 --> 08:16.120 And remember that these are teachers and I have only George Steve email and global and teacher and pizza 08:16.120 --> 08:17.710 is no longer there. 08:17.740 --> 08:18.370 Check it out. 08:18.400 --> 08:23.740 Pizza is in the first one for students or does not exist for the teachers. 08:23.740 --> 08:25.030 So what happened? 08:25.390 --> 08:30.410 We are going to show everything by using what is called Union Oil. 08:30.440 --> 08:33.050 And go ahead and execute this query and check it out. 08:33.530 --> 08:40.070 Now you have to first on the students and you have to train under the teachers as well. 08:40.310 --> 08:41.270 I hope that is cool. 08:41.270 --> 08:49.670 So that is a way whenever I use the union or the union, all will help you to be able to display the 08:49.820 --> 08:53.840 three students or the data that are duplicate. 08:53.840 --> 09:00.650 But if you use only union is going to display just one if you have a duplicate data. 09:00.680 --> 09:06.980 So always use union all in order to be on the safe side, so you can be able to print out both the duplicates 09:06.980 --> 09:07.460 data. 09:07.490 --> 09:08.870 So that is all for now. 09:08.870 --> 09:11.030 That is about Union and MySQL. 09:11.060 --> 09:15.170 Play around with it and if you have any questions, why had I used the question and answer section. 09:15.170 --> 09:17.600 And I'm going to get back to you as soon as possible. 09:17.630 --> 09:21.320 Thank you so much and I'm good to see you in the next video lecture.