Detailed Notes
Notes from the tutorial:
1. More data insert into tutorial_parent(username,email,userLogin,pwd) values ('user 3','[email protected]','user3','testuser3'); insert into tutorial_parent(t_one_id,username,email,userLogin,pwd) values (1,'user 4','[email protected]','user4','testuser4'); 2. select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip from tutorial_parent as tp inner join tutorial_one t1 on tp.t_one_id=t1.t_one_id;
select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip from tutorial_parent as tp,tutorial_one t1 where tp.t_one_id=t1.t_one_id;
select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip from tutorial_parent as tp left join tutorial_one t1 on tp.t_one_id=t1.t_one_id;
3. insert types insert into lkp_type(code,name) values ('HOME','Home Address'); insert into lkp_type(code,name) values ('MAIL','Mailing Address'); insert into lkp_type(code,name) values ('BUS','Business Address'); 4. alter table ALTER TABLE tutorial_one ADD type_id int null AFTER zip; ater table tutorial_one add constraint type_fk FOREIGN KEY (type_id) REFERENCES lkp_type(type_id)
5. select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip,lt.name from tutorial_parent as tp left join tutorial_one t1 on tp.t_one_id=t1.t_one_id inner join lkp_type lt on lt.type_id=t1.type_id
select tp.username,tp.email,tp.userLogin,t1.address_name,t1.city, t1.state,t1.zip from tutorial_parent as tp,tutorial_one t1, lkp_type lt where tp.t_one_id=t1.t_one_id and lt.type_id = t1.type_id;
Transcript Text
[Music] three two one well hello and welcome back we are continuing our mysql sql database uh sorry my sequel miss mariadb sql tutorial stuff and we're going to continue talking about tables today so if we go ahead and jump into whoops i should probably get out to my machine and then uh let's see let's do what are we going to talk about today okay so first thing we're going to do is we're going to jump into our [Music] uh jump into our database tutor dash p and we are going to use our tutorial database yep if you remember last time we had two tables we had a table one and parent table one is sort of like an address and um the parent is like a user and so what i want to do this time is i'm going to take a couple records and insert them into the parent table because what we do here is we're going to play around a little bit with the inner and outer joins now what we have here is uh some data let's do this from tutorial parent i want to show you the data real quick that we have first so that's what we have in parent and in one we've got so what we have for we'll call them users we've got four users and we've got three addresses and if you remember from foreign key relationships we have in the parent table we have this t1 id which ties it back to a record in the tutorial 1 table and so we have two of the records here are tied to record three one isn't tied to anything and one of them is tied to record one now if we go back to i'm gonna steal my query here that we used from last time and this query we're selecting from the parent the username email login and then from the address or the tutorial1 table address name citystate zip and as we can see here although we have and we did a select you know we didn't have anything else anywhere clause or anything so theoretically it should take because it did from uh tutorial parent it should give us all the records back in tutorial parent however it doesn't because we have this inner join that says i'm only going to take back rows where my t1 id exists somewhere in the other t1 id and so that's where we get the results here where we don't see user name three because they're not tied to anybody but we also do see here where username one and two have the same address we can see here they're both tied to address two and then the other guy is tied to address four now that is an inner join but if we just do an outer join or more specifically a left join and change that keyword inner to left that's the only thing i'm going to change now we're going to see all of them because what this says is it says go ahead and give me the data from where does the left join give me the data from tutorial one let me see if i expand that out it'll be a little bit yeah so give me the data from tutorial one but only if i can connect to it what it does otherwise is it sends us nulls so we can see here for user three where what we have is a user that's tied to no address is guess what it doesn't show us an address it just shows us nulls from this address name city state and zip and all of those fields came out of the tutorial one now i do want to before we move on i want to do a it's it's an old-school way to do joins and i'm going to show you just because you may run into it but this approach of using of specifically doing like inners or inner or left joins is a much better way to do it and the way that looks i can't blow that up enough probably so what i'm going to do instead i'm going to take the inner join and then we'll show it i'll show it to you in a second here is instead i'm going to take the same selects but i can say here where i said from tutorial parent as tp then i can do a comma and give it the second table which is going to be tutorial one or as many tables as i want and then instead of this whole inner join then i'm going to say where tp1 equal or tp one id equals t1 one id and i'll do this here so it's a little bigger to see and so here we go is so we have the same fields what we do is instead of seeing multiple basically you think of as like a line per table that we're joining we see all of these um here we go from our two here's our two tables and then we just have a where clause so it's just select stuff from a list of tables and then a where clause the problem is is that if we do the same thing and we don't include a where clause we get every possible combination you get a cross join where it basically says for every row in the first table which is tutorial parent pair it with a as in p not parrot is in the bird but p a i r the record with a record in tutorial one so you can see here user one exists three times one for each record that's in tutorial one and so you can see here where he has name two three and four for user one and then here you see the same thing repeated for two and so you end up with these things that are really not what you wanted and as you can see here can be huge i mean this was just three records by four records let's say you do that with uh two tables that have 10 000 records each you have just suddenly created a huge query huge result set none of which is useful now there are some things you can do to try to uh simplify that out a little bit but that would be after the fact and i think that's part of why we've moved to this uh this use of inner joins and left and inner left outer joins using those is that now it's much easier to see what the you give it like a primary table and it starts there and then it's going to start whittling away before it even leaves before that data even leaves the server which makes it easier to tune and you're going to find that your queries are dramatically different in speed if i were to take this same query and i could if i did this query with the where that one that did work you know that one only gave me the three but if i used that along with the one that was the inner join where was the inner join um that's left joint here if i do that same one so you can see here these are the identical result sets and for all these are identical queries i guess as far as like this is what i'm asking but how i ask it is different and in a lot of cases a lot of modern databases you will find that using the inner join approach like this is going to be substantially faster i have seen a pretty nice percentage uh as as far as like set up to like 70 and 80 percent better running queries and maybe even more than that doing it with this inner join this inner join as opposed to doing like the multiple table listing and it also it helps with it we'll get into in the idea of indexes and stuff like that but this is going to be just showing you this so that you can see it but you want to use this this inner join approach so now let's go back over here now let's go ahead and i'm going to create a new table this third table i'm going to create i'm going to call it lookup type or lkp type and what you can see here is all it is is it's a type id it's a 100 up to 100 character name i'm going to default it to undefined i'm going to have a code that's a varchar4 and then its primary key is type id and so now i'm going to because i cheated a little bit earlier i have somewhere in here some sql to create a few rows so i'm going to create those rows and whatever i did i didn't do right so let's go back to this it's called lookup type oh that's not insert into table that's what happens when you don't pre-run your stuff but that's okay at least i'm saving us a little bit of time and okay and then if i do select star from lookup type then i'm going to see the three records i created with their id so now i've got this idea of a home address a mailing address and a business address and what i'm going to do is i'm going to go into tutorial 1 table where we see our addresses and i'm going to add a type a type field because we're going to get a little more complicated here so first thing i'm going to do is i'm going to add a column so i'm adding a column called type id to tutorial 1 and it's going to show up after zip so now if i do describe tutorial 1. i'm going to see that i have now this type id and by default it's null and if i do this select star from tutorial 1 then i'm going to see that i have the type id of null everywhere now let's just make it simple and i'm going to update well let's do it this way first now what i'm going to do is if you can remember we used a reference in the past we used a foreign key reference uh we use this constraint in order to tie a address essentially what an address is back to a person and so we're going to do the same thing but we're going to use an alter so what we can do is we can alter the table and i'll show you here is oops i have a typo in there anyways so i'm going to come in like we saw before i'm going to alter table i'm going to give it a table name so this is going to be that tutorial 1 table and now i'm going to add a constraint before we added a column i'm going to give it the constraint name what type is it and then basically you know what key or what com what column which would be type id am i using in tutorial one and then what am i tying it off to i'm going to tie it to lookup type type id i get it there we go and so now going back to my type ids if i wanted to update tutorial 1. and i want to set the type id equal to 4 let's do that because look there is we'll note that there is no 4 so it should complain as you remember because that foreign key is going to kick off here in a second and sure enough foreign key constraint fails uh the type fk and that's the name of the that's my foreign key and it does because i can't find number four in there but if i change it over to three then boom i've got it and if i go select star from tutorial one now everybody's type is going to be type three which is a business address so now if we had now we've got three tables and so that's where i want to go next is i'm going to take our earlier one let's do this get my notes changed a little bit and i will show you this a little better in a bigger font so now what i'm going to do is i'm going to actually do two inner joins it's i'm going to interjoin the table lookup type and i'll call him and i'm going to join that on lt dot type id equals he is in tutorial 1 which is t1 type id and so now all i'm going to do is i'm just going to take after all of this i'm going to come in here i'm going to do an lt dot uh we'll do that code for now and so if we take that and we jump over in our database so no this is i haven't changed too much is i'm saying i'm going to add another table that i'm going to link data to and i'm only going to show just one additional field and so doing that now i see that i have my where is it i have my tutorial parent data now i can see my tutorial child data and now i can see my lookup type and i could change that so i could call it instead of uh oh wow let's do it this one that's it'll be easier to edit it here so let's say instead of code i say name and i can make either of these a left join so i come in here and do a left join and i can do this and make this a left join we'll make both of them and then you're going to see that it'll be able to flow through and i see nulls across the board here now if i were to do an inner join on this first one the inner join back to the address then this row would disappear because it would say i have to have an address but now if i do an inner join here but leave a left join for the type i'm sorry leave a left join and then do an inner join on the type then i'm also going to lose it we'll see here because even though i did a left join for the address i said were said hey give me nulls if you know if you find nulls that's fine but i came back with the inner join and said i have to have a matching name and so now i'm stuck because it says hey i'm looking up the code you gave me a null i can't find the null code anywhere so that's not going to work and so you can you know mix and match your inner joins and left joins but realize that once you do one that's entered you're going to restrict some of your some of the data that comes back so it can be a little bit complicated and that's why i wanted to get us to at least three tables now if we want to take our earlier example [Music] i'll show you how it gets really complicated quick if we do our one with the commas uh that's the inner join where did i put that nope not that one let's create table it's another here we go so let's take our earlier one where we did the comma version so we did so we do a select from table 1 comma table 2 comma table three lookup type lt now if we want to do the inner join we're going to say where the t1 ids match and lt dot code id or type id equals was that t1 dot type id and so you end up having these really huge where clauses yep there we go and so i just pasted that in and you have these really huge where clauses but i do get the right data it's just now which is basically a processing thing it's now it's basically pulling everything and then it's going to start pulling it out as opposed to while it's pulling data limiting what it's pulling even in the first essentially like the first pass now i think that's a good spot for us to stop on this one we've added a third table we've got some additional data and we're cruising along learning a little bit more about our you know our selects we've touched on some updates and things like that and so now we're going to get we'll continue moving on the next episode that being said we'll wrap this one up so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
three
two
one
well hello and welcome back
we are continuing our mysql sql database
uh sorry my sequel miss mariadb sql
tutorial stuff
and we're going to continue talking
about tables today
so if we go ahead and jump into
whoops
i should probably get out to my machine
and then
uh let's see let's do
what are we going to talk about today
okay so first thing we're going to do is
we're going to jump into our
[Music]
uh
jump into our database
tutor dash p
and we are going to use our tutorial
database yep
if you remember last time we had
two tables
we had a table one
and parent table one is sort of like an
address
and um
the parent is
like a user and so
what i want to do this time
is i'm going to take a couple records
and insert them
into the parent table
because what we do here is we're going
to play around a little bit with the
inner and outer joins
now what we have here
is uh some data let's do this
from
tutorial parent
i want to show you the data real quick
that we have first so that's what we
have in parent and in one we've got so
what we have for we'll call them users
we've got four users and we've got three
addresses
and if you remember
from foreign key relationships
we have in the parent table we have this
t1 id which ties it back to a record in
the tutorial 1 table
and so we have two of the records here
are tied to record three
one isn't tied to anything and one of
them is tied to record one
now if we go back to
i'm gonna steal my
query here that we used from last time
and this query we're selecting from the
parent the username email login and then
from the address or the tutorial1 table
address name citystate zip
and as we can see here although we have
and we did a select
you know we didn't have anything else
anywhere clause or anything so
theoretically it should take
because it did from uh tutorial parent
it should give us all the records back
in tutorial parent however it doesn't
because we have this inner join that
says i'm only going to take back rows
where
my t1 id exists somewhere in the other
t1 id
and so that's where we get the results
here
where we don't see
user name three
because they're not tied to anybody
but we also do see here where username
one and two
have the same address we can see here
they're both tied to address two and
then the other guy is tied to address
four
now that
is
an inner join but if we just do an outer
join or more specifically a left join
and change that keyword inner
to left that's the only thing i'm going
to change
now we're going to see all of them
because what this says
is it says
go ahead and give me
the data from
where does the left join
give me the data from tutorial one let
me see if i expand that out it'll be a
little bit yeah
so give me the data from tutorial one
but only if i can connect to it what it
does otherwise is it sends us nulls so
we can see here
for user three
where what we have is a user that's tied
to no address is guess what it doesn't
show us an address it just shows us
nulls from this address name city state
and zip and all of those fields came out
of the tutorial one
now i do want to
before we move on i want to do a it's
it's an old-school way to do joins
and
i'm going to show you just because you
may run into it
but this
approach of using of specifically doing
like inners or
inner or left joins is a much better way
to do it
and the way that looks
i can't blow that up enough probably
so what i'm going to do instead i'm
going to take the inner join and then
we'll show it i'll show it to you in a
second here
is instead i'm going to take the same
selects
but i can say
here
where i said from tutorial parent as tp
then i can do a comma and give it the
second table which is going to be
tutorial one or as many tables as i want
and then instead of this whole inner
join
then i'm going to say
where
tp1 equal or tp
one id equals t1 one id and i'll do this
here so it's a little bigger to see
and
so here we go is so we have the same
fields
what we do is instead of seeing multiple
basically you think of as like a line
per table that we're joining
we see all of these
um here we go from our two here's our
two tables
and then we just have a where clause so
it's just select stuff from a list of
tables and then a where clause the
problem is
is that if we do the same thing
and we don't include a where clause
we get
every possible combination you get a
cross join
where it basically says for every row
in the first table which is tutorial
parent
pair it with a as in p not parrot is in
the bird but p a i r the record with a
record in tutorial one so you can see
here
user one exists three times
one for
each record
that's in tutorial one
and so you can see here where he has
name two three and four
for user one and then here you see the
same thing repeated for two
and so you end up with these things that
are
really not what you wanted and as you
can see here can be huge i mean this was
just three records by four records let's
say you do that with uh
two tables that have 10 000 records each
you have just suddenly created a huge
query huge result set
none of which is useful
now there are some things you can do to
try to
uh simplify that out a little bit
but that would be after the fact and i
think that's part of why
we've moved to this
uh this use of inner joins and left and
inner left outer joins using those
is that
now it's much easier to see what the
you give it like a primary table and it
starts there
and then it's going to start whittling
away before it even leaves
before that data even leaves the server
which makes it
easier to tune
and you're going to find that your
queries are dramatically different
in speed if i were to take this same
query
and i could
if i did this query with the where
that one that did work
you know that one only gave me the three
but if i used that along with the one
that was the inner join where was the
inner join
um
that's left joint here if i do that same
one so you can see here
these are the identical result sets
and for all these are identical
queries
i guess as far as like this is what i'm
asking but how i ask it is different
and in a lot of cases a lot of modern
databases you will find
that using the inner join approach like
this is going to be substantially faster
i have seen
a pretty nice percentage uh as
as far as like set up to like 70 and 80
percent
better running
queries and maybe even more than that
doing it with
this inner join this inner join as
opposed to doing like the multiple table
listing
and it also it helps with
it we'll get into in
the idea of indexes and stuff like that
but
this is going to be just showing you
this so that you can see it but you want
to use this this inner join approach
so now
let's go back over here now let's go
ahead
and i'm going to create a new table this
third table i'm going to create
i'm going to call it lookup type or lkp
type
and what you can see here
is all it is is it's a type id
it's a 100 up to 100 character name i'm
going to default it to undefined i'm
going to have a code that's a varchar4
and then its primary key is type id
and so now i'm going to because i
cheated a little bit earlier i have
somewhere in here
some sql to create a few rows
so i'm going to create those rows
and whatever i did i didn't do right so
let's go back to this
it's called lookup type
oh that's not insert into table
that's what happens when you don't
pre-run your stuff but that's okay at
least i'm saving us a little bit of time
and okay and then if i do select star
from lookup type
then i'm going to see the three records
i created with their id so now i've got
this idea of a home address a mailing
address and a business address and what
i'm going to do is i'm going to go into
tutorial 1 table where we see our
addresses and i'm going to add a type
a type field
because we're going to get a little more
complicated here so first thing i'm
going to do is i'm going to add a column
so i'm adding a column called type id
to tutorial 1 and it's going to show up
after zip so now if i do describe
tutorial 1. i'm going to see that i have
now this type id
and by default it's null and if i do
this
select star from tutorial 1
then i'm going to see that i have the
type id of null everywhere
now let's just make it simple
and
i'm going to update
well let's do it this way first now what
i'm going to do is if you can remember
we used a reference in the past we used
a foreign key reference uh
we use this constraint in order to tie
a address essentially what an address is
back to a person
and so we're going to do the same thing
but we're going to use an alter
so what we can do
is we can alter the table
and i'll show you here
is oops
i have a typo in there anyways so i'm
going to come in like we saw before i'm
going to alter table i'm going to give
it a table name so this is going to be
that tutorial 1 table
and now i'm going to add a constraint
before we added a column
i'm going to give it the constraint name
what type is it
and then basically you know what key or
what com what column which would be type
id am i using
in tutorial one and then what am i tying
it off to i'm going to tie it to lookup
type type id
i get it
there we go
and so now
going back to my type ids if i wanted to
update
tutorial 1.
and i want to set the type id
equal to 4 let's do that because look
there is we'll note that there is no 4
so it should complain as you remember
because that foreign key is going to
kick off here in a second and sure
enough
foreign key constraint fails
uh
the type fk and that's the name of the
that's my foreign key and it does
because i can't find number four in
there but if i change it over to three
then boom
i've got it and if i go select star from
tutorial one now everybody's type is
going to be type three
which is a business address
so now if we had now we've got three
tables and so that's where i want to go
next is i'm going to take
our earlier one
let's do this
get my notes changed a little bit
and i will show you this
a little better in a bigger
font
so now what i'm going to do is i'm going
to actually do two inner joins
it's i'm going to interjoin
the table lookup type
and i'll call him
and i'm going to join that on lt dot
type id equals
he is in tutorial 1
which is t1 type id
and so now all i'm going to do is i'm
just going to take
after all of this
i'm going to come in here i'm going to
do an lt dot
uh we'll do that code for now
and so if we take that and we jump over
in our database
so no this is i haven't changed too much
is i'm saying i'm going to add another
table that i'm going to link data to
and i'm only going to show just one
additional
field
and so doing that
now i see
that i have my
where is it i have my
tutorial parent data
now i can see my tutorial child data
and now i can see
my lookup type
and i could change that so i could call
it instead of
uh oh wow let's do it this one that's
it'll be easier to edit it here so let's
say instead of code i say name
and i can make either of these a left
join so i come in here and do a left
join
and i can do this and make this a left
join
we'll make both of them and then you're
going to see
that it'll be able to flow through
and
i see nulls across the board
here
now
if i were to do an inner join on this
first one the inner join back to the
address
then this row would disappear because it
would say i have to have
an address but now
if i do an inner join
here
but leave a left join for the type
i'm sorry leave a left join
and then do an inner join on the type
then i'm also going to lose it
we'll see here
because
even though i did a left join for the
address i said were said hey give me
nulls if you know if you find nulls
that's fine
but
i came back with the inner join and said
i have to have a matching name
and so now
i'm stuck because it says hey i'm
looking up the code
you gave me a null i can't find the null
code anywhere so that's not going to
work
and so you can you know mix and match
your inner joins and left joins but
realize that once you do one that's
entered you're going to restrict some of
your some of the data that comes back
so it can be a little bit
complicated
and that's why i wanted to get us to at
least three tables now if we want to
take our earlier example
[Music]
i'll show you how it gets really
complicated quick if we do our
one with the commas
uh
that's the inner join
where did i put that
nope not that one
let's create table
it's another
here we
go so let's take our earlier one
where we did the comma version so we did
so we do a select from table 1 comma
table 2
comma
table three lookup type
lt
now
if we want to do the inner join we're
going to say where
the t1 ids match and
lt dot
code id or type id
equals was that t1
dot type id
and so you end up having these really
huge
where clauses yep there we go and so i
just pasted that in
and you have these really huge where
clauses but i do get the right data it's
just now
which is basically a processing thing
it's now it's
basically pulling everything and then
it's going to start pulling it out as
opposed to
while it's pulling data limiting what
it's pulling even in the first
essentially like the first pass
now
i think that's a good spot for us to
stop on this one we've added a third
table we've got some additional data
and we're cruising along learning a
little bit more about our you know our
selects
we've touched on some updates and things
like that
and so now we're going to get we'll
continue moving on the next episode that
being said we'll wrap this one up so go
out there and have yourself a great day
a great week and we will talk to you
next time
you