join

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