I will call you back again to another video lecture in MySQL.
And in this video lecture, let's go ahead and learn about the union clause.
So the union clause is used to combine the results of two or more Select statements in MySQL.
So let's go ahead and say we have this table right.
And this is our students.
And remember we are dealing with my school database.
So let me say the owner of the school jumped in and say hey, the database manager, I need you to provide
me every name of teachers and their subject in two columns only.
All right.
Let's go ahead and check.
First of all, go ahead and select, uh, the teachers table and the subjects table.
Let's check it out.
So select all from the teachers table and let's check it out.
So this is a test table which you have x and it has only two columns.
Good.
Now let's check the subject's table and see what he looks like.
Subject's.
And the subject's table has two columns as well.
Fine.
That is good.
Now we have two columns to deal with, so no problem at all.
Then after.
What we need to do now is we want to give the owner of the school these subjects and teachers in just
two columns only.
So what I need to do is go ahead and add union, and then I will also select uh oh from teachers, press
semicolon at the end.
And let's go ahead and run this code and check it out.
So here is a query.
And this is what we have.
And you can see we have subject ID and subject name as the name of the columns.
And because we selected subject first the subjects are on top and our teachers are below.
So you can see teachers first the ID one George Steve M-1 global and the teachers ID 105 106 107 108
because this has an Autoincrement.
And we're able to insert some things into here and uh, physics, chemistry, biology, history.
So if you followed me from the beginning of this course, you can see that when you did this, you were
able to or you see teachers ID here making the subjects table to have three columns.
Go ahead and drop the foreign key and also drop the teachers ID to have just two columns.
That is what I did.
Now I was able to have this.
If I go ahead and change this to be teachers, and then I change this to be subjects, then the teacher
should be on top and the subjects is going to be below.
So teachers ID and the first, the first name that is the column names.
And you can see here we have the teachers ID first, the teachers the first name here.
And then we have the subjects below.
So you can now print out this and give to the school and say, yes, I've been able to print this out
and that is it.
All right.
Now know you can be able to add two tables using the union and then no problem.
What if I have the teachers table?
Let's say this is teachers table.
And the other school comes back and says, okay, I need every teacher and every student in this in
just two columns.
Go ahead and say, let's check out the students.
So these are the students.
I check it out.
And this is what we have about 123456.
We have six columns.
And this might give us problem.
So let's go ahead and check it out and say union select all from teachers.
And now let's go ahead and execute the query and check it out.
Nothing happens.
And I'm going to drag this so I can be able to see.
And the error here says error code 1 to 2.
The youths select statement have a different number of columns And that is the problem.
Yes, that is a problem.
So what actually happens?
So under the teachers we can see that there are if you go ahead and select teachers only teachers first
name and students have first name.
So why not go ahead.
Go back right in here and say remove all and select only the first name.
All right select teachers ID and select the first name.
So go ahead and say select A students.
Go back over here.
Let's go ahead and put this back again and check the students.
And the student has students ID and first name.
So select stress ID and first name.
And then we go back right in here and check the teachers and teachers has teachers ID and the first
name.
Then go back right in here and remove the all from both of them.
Now go back right in and say for the students I'm going to select a students underscore ID then comma
and uh, I had um select underscore first name.
Then for the teachers, I'll go ahead and select a teachers underscore ID and uh, the first name.
Then let's go ahead and check it out.
Well students ID because we select from students first.
And this go ahead and drag this up and check it out.
So we have Peter Oscar Paul rocks Mike.
Uh Mike Queen Zendaya.
Then the teachers you can see the teachers are listed judge Steve Steve and global.
Wow.
Hope that is cool.
Oh well all right.
Now that is it.
Very simple.
So you can be able to select the columns that are not of the same.
Any columns that are not the same in nonviolent you can be able to select that.
So you just go ahead and specify what you want to select.
All right.
Now what if this can actually print out only names that are unique.
Yes.
It can only print out names that are unique.
Let's go ahead and remove everything right in here.
And now go back and insert into teachers.
Go ahead and insert into teachers.
And I'm going to insert these values there.
So inside this values I'm going to say for teachers I'll go ahead and insert Peter.
And then I'll go ahead and select these so I can be able to see it.
Select all from teachers and police.
Semicolon and end.
So let's go ahead and say maybe this is five.
And then we go ahead and uh check out.
So let's know what is the problem.
So put semicolon at the end of this.
So we have semicolon there.
And that is it.
Now run this code and check it out.
Now we have another one called a pizza.
And I can see it's starting to touch the walls.
Five and now we have these.
I want to go back to our code and we're just going to do this.
So I moved back right in here.
And I want to select students and teachers.
And I did a union.
Now let's go ahead and check if we printed out.
Go ahead and check this out.
Now we have pizza printed out here and here.
Meaning pizza is already there.
Okay.
Now what if I remove this first name or remove the IDs and then this code hadn't executed query and
I have pizza and a half.
Go back here.
I have pizza just once.
And remember that these are teachers and I have only George Steve email and global and teacher and pizza
is no longer there.
Check it out.
Pizza is in the first one for students or does not exist for the teachers.
So what happened?
We are going to show everything by using what is called Union Oil.
And go ahead and execute this query and check it out.
Now you have to first on the students and you have to train under the teachers as well.
I hope that is cool.
So that is a way whenever I use the union or the union, all will help you to be able to display the
three students or the data that are duplicate.
But if you use only union is going to display just one if you have a duplicate data.
So always use union all in order to be on the safe side, so you can be able to print out both the duplicates
data.
So that is all for now.
That is about Union and MySQL.
Play around with it and if you have any questions, why had I used 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 good to see you in the next video lecture.