How to join
2 Table In Mysql
MySQL tutorial 15 - joining 2 tables
Sharing buttons:
hey YouTube how's it goin it's Quinton
here and welcome to tutorial number 15
and in this tutorial I want to talk to
you guys about joining different tables
so this is something I kind of briefly
touched on in a previous tutorial I
think it was when I spoke about creating
tables and I said that sometimes when
you create a table you want to have a
field in the table you're creating to
match another table that already exists
in your database and that's exactly what
joining two different tables is for and
it's really useful because it allows us
to not have to repeat data from one
table and put it in another table now
all of that cannot gues be a little bit
confusing so that is what we're going to
be explaining and sort of demonstrating
in this video but before I get started I
just want to send a huge shout out to my
sponsors at dev Mountain so dev Mountain
is a coding bootcamp that'll teach you
everything you need to know to get a job
as an iOS developer a UX designer or a
web developer and they'll teach you all
of that stuff within 12 weeks so if you
are looking to make a career change and
you're looking to make a career change
quickly go ahead and check out their
website the link is in the video
description below and if you do contact
them make sure to tell them that I thank
you right so now let's take a look at
our tables real quick because there is
something I want to talk about before I
actually write my my SQL query and that
is if you go over to the addresses table
I want to explain the concept of
something called a foreign key so a
foreign key is a primary key of another
data or of another table within your
database so in this case if you look at
the addresses table you can see that we
have an address ID and that is a primary
key hopefully by now you know what a
primary key is because I have a video
explaining this early on in the series
so hopefully you know what that is but
if we look at the column below that you
can see that our second column is a
column for a users ID and the users ID
in this case is a field that links to
our users table and it links to the ID
field
within the users table so basically yeah
let's go to the addresses table let's
browse the addresses table and you can
see that we have a user's ID one a
user's ID 15 and in the last two we have
user's ID 12 and 12 right so that's a
let's let's go over to the users table
and see who those people are
let's go browse and you can see user's
ID one is Quinton user ID 15 his record
Relf and user ID 12 is Cartman so
Cartman has two addresses within the
addresses table alright and yeah instead
of actually looking at the two different
tables using the Browse column in
phpMyAdmin let's learn how to write
natural query with this so let's go over
the SQL tab and let's get rid of
everything that's in here and type in
our own query so yeah I just want to
link these two information from the
users table and information from the
addresses table together and show them
within the same query so the way I do
that is I type the word select obviously
to select something from our database
and then I need to be a little bit more
specific so in previous tutorials we
always just typed in a column name like
that
well not all in caps but yeah we wrote a
column name like that now the difference
between selecting from one table and two
tables is the fact that you have to be
specific so you have to put in a table
dot and then a column so in this case if
we want to select something from the
users table we'll type uses dot and then
whatever column we want to select from
the users table which in this case I
think we can select the users name so
let's go to user don't user name and
that will select the users user name we
can also select the users dot first-name
and the users dot last name so that's
going to select three fields from our
users table now let's take a look at how
to select some fields from our addresses
table and that is going to be as simple
as typing addresses dot and then
whatever field you want to select from
the addresses table so
select cities and addresses dot
state/province and I think let's add one
more column in here so addresses dot
Hank address line one fortunately we've
got this little autocomplete shortcut
thing here so that tells us what they
are what columns are in each table right
so now we've got three columns from our
users table and then we've also got
three columns from our addresses table
and before we can hit go on this query
we're going to need to finish the query
off so we've selected our columns now we
need to select the tables that we're
selecting them from and I know it's kind
of redundant because like you'd think
okay this is already telling the query
what table but actually it really isn't
we still need to say from users and say
join to select our second table so join
and then select the second table which
in this case is going to be addresses
and I think I actually want them the
other way around so let's go select from
addresses and users now the reason why I
want them the other way around
is because not everybody in the
addresses table has an address so if we
do it this way around we'll only select
information that's in the addresses
table if we do it the other way around
where users is there then we're going to
select everyone that has a everyone that
is a user even if they don't have an
address
so yeah doing it this way around it just
means that we're only going to get
information from this table and like
leave out anybody that doesn't have any
matching data all right and now the last
thing you need to do is type the keyword
on and then you need to tell my sequel
where these two tables are linked what
joins them and we already know that that
is going to be the foreign key which I
spoke about in this case which is
addresses dot user ID and we want to set
where that matches users dot ID right so
basically what this query does is enough
she'll in a nutshell is select
information from those columns from
these two tables where these two fields
match that's the the simplest
explanation I know it is kind of a long
query though but let's hit go and as you
can see the query has run successfully
and we have successfully selected a
username a first name a last name an
address line 1 and a city in a state or
province from each row so we've selected
from the users table and then the
addresses table and you can see that
Quinn's in what has an address in Cape
Sun you can also see that Cartman has an
address and 28 201 Bonanza Street and he
also has another address on 635 Avenue
dustless mexicanos right so yes that is
how to join two different tables and I
know you guys are probably wondering why
Cartman got repeated it's just because I
wanted to set up the ability for users
to be able to have multiple addresses so
that's why it's set up like this it's
just a cool little feature but it has
absolutely nothing to do with the join
query so now let's take a look at more
joins all right so one join that we
looked at is just the simple join query
which by default is actually an inner
join so inner join and join it's the
same thing if you hit go you're pretty
much going to get the same results all
right but there are different types of
joins there's also something called a
left join there's also something called
a right join and there's something
called a full join and you might want to
take a look at the difference between
all of these so I'm going to leave this
link in the video description below but
yeah if you take a look at this link
there is a cool little diagram that
shows you the difference between an
inner join a left join a right join and
a full join and basically an inner join
is where both of the tables are matching
a left join will select everything from
the left table and
in it on the right table where the left
matches the right a right join is going
to select everything from table 1 and
table 2 but join them where everything
on the right matches the left and then
we have a full join which will select
everything from both tables and join
them where they both match each other so
yeah there is a difference between the
two
well difference between the 4 but let's
take a look at the left join first and
in this case the left join is going to
select everything from the addresses
table because that's on the left and
join it on the right table where these
are matching columns so let's hit go and
you can see that that has now selected
everything and the query pretty much
looks the same as the query result
pretty much looks the same as previously
where everything from the users the
addresses table has been selected but if
we go back and modify this query a
little bit so instead of saying left
let's say right now uses is on the right
and addresses is on the left so we're
actually going to select everything from
the right table and join where the right
matches the left so in this case if I
hit go now we've selected everything
from the users table so even users who
don't have addresses have been selected
in this case so guys like Shawn zebu
ryan christie none of them have
addresses set up which means that all
the information under the address fields
for them has been set to no so that is
kind of the difference between left join
and a right join and also again you can
run a full join and take a look at the
difference for that as well your got an
error somewhere right and I think this
error is actually appearing because
there are people who don't have
addresses like I showed you with the
right join so basically all of these
people who don't have a dress
are basically making my fool join fail
so that is something to keep in mind
whenever you want to use a full join but
yeah in most cases when you're running
queries a simple join statement just
like this which is actually by default
something called an inner join is simple
enough to get all the information that
you need together so you don't have to
worry about the rest of the stuff and
yeah now that we have spoken about joins
I hope you guys can practice this maybe
try with a few less fields in your query
or try with a few more fields in your
query and see if you can come up with a
challenge and before I end up this
tutorial I want to send a huge shout out
to you guys so these guys are my patrons
who contribute five dollars or more
every single month and I really do
appreciate that so if you guys like my
videos and you want to help me make more
videos more often then go ahead and
check out my patreon page and consider
making a pledge also don't forget to
subscribe like share and leave a comment
on this video because that is really
going to help my channel grow and I'll
see you guys next time
![how to join Multiple table in mysql [ Inner Join , Left Join, Right Join ]](https://img.youtube.com/vi/lKPQ94VjySI/mqdefault.jpg)
