Detailed Notes
1. inner joins vs outer joins
insert into stats(code,value1,value2) values ('TEST',6,12345);
select s.code,l.code from stats s inner join lkp_type l on l.type_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s inner join address addr on addr.t_one_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1 where addr.city is null;
select s.code,addr.city,addr.state,addr.zip,l.code from stats s left join address addr on addr.t_one_id=s.value1 left join lkp_type l on l.type_id=addr.type_id;
select s.code from stats s where s.value1 not in (select type_id from lkp_type);
select s.code from stats s inner join (select type_id from lkp_type where code='HOME') src on src.type_id=s.value1;
select s.code,addr.city,addr.state,addr.zip from stats s left join address addr on addr.t_one_id=s.value1 where a;
select s.code,l.code from stats s left join lkp_type l on l.type_id=s.value1 where l.code='HOME' or l.code is null;
Transcript Text
[Music] well hello and welcome back we are continuing our tutorials of sql focus on mysql and mariadb and uh we're gonna change pace a little bit uh we've gone through stored procedures and stored functions and looked at those and now we're going to get a couple of issues or a couple of sessions tutorials i guess we're going to talk a little bit about uh approach and it has to do with performance tuning and things like that to some extent but also just to talk through some ways to approach queries and where clauses and things of that nature this is going to be it's really more like not really necessarily suggestions as much as options because i think you'll find as you get into these things that sometimes there's certain approaches that you're going to need at certain times and it just that's the way it's going to work but they're also going to be situations where you have different approaches and how you do it will impact speed things like that because your your goal is to leverage as much as possible the indexes and other built-in pieces of the database that allow you to quickly query and indexes are probably the biggest thing and then we'll we will talk a little bit as we get further into us about some different types of indexes and a little bit more on those specifically but today i really just want to talk about we're going to look at inner and outer joins and so the first thing i want to do is we're jumping back into some of the data that we've seen before and we had this table uh stats and it just has you know some codes some values i'm gonna do is i'm going to add another record in there and the key here is value one because what i had before is i had value one values were from one to three now i'm going to add one that's got a value of six and it's particularly because this table is not tied to anything else and we're going to go back and look at the lookup type [Music] and based on the ids we only had numbers one through four so if we wanted to for some reason you know if if this was just you know we're just playing around these tables if we wanted to do select star uh let's say let's do this let's do select code and well we're gonna do this a little bit so we're gonna like select code from uh stats so that just gives us those but now what we want to do is we want to do uh stats so we're going to alias him as s so that's just be the stats so we're going to do s dot code comma l dot code and let's say we do an inner join look up type l on l dot type id equals s dot value 1. [Music] so what we want to do now is we're going to say okay for those values in value 1 where what would be the the lookup type for those now if we do this with an inner join [Music] we're going to get for each of those we're going to match but notice for our test record that we created it doesn't exist down here because it's an inner join it says hey if that value doesn't exist then it's not part of the result set now if we wanted that to show up anyways all we have to do is we do a left join [Music] and now we're going to see that record does show up but it has a null value now what we're getting here the two different result sets is because of the inner join it limits um records that are coming back from stats now that's not always what we want to do in some cases we're going to want essentially all of the records to come back but like in this case let us know if that value doesn't exist so we could do this through foreign key relationships and things like that we can force a value to exist but in some cases it won't and particularly once you get into uh comparisons and things like that you're gonna have like uh and even for example you may have things like contacts where you may or may not have an address record and maybe you want to go through and say okay give me a list of all my contacts if i have an address i want to see it but if i don't then that's okay so you could show me for example a null and we can do the same thing um we had for that show tables uh what do we call address okay let's see what our address values are oh so we can do the same thing so now let's take that same one and now instead of lookup type let's do address and we're going to call it a uh we'll call it adder and on adder.something equals that and here uh let's do address name well this is city state oh that's a dot [Music] and zip [Music] and instead of it being value one we're going to do it on t1id [Music] one id so now paste that in so now what we're doing is we're saying hey if that links up to an address let's see it i want to see the city state and zip for that well again we're now seeing that we have some records that don't uh that don't show up we had here we had eight rows now we're down to oh i'm sorry not eight rows nine rows when we do a select star but here we're gonna get eight so we say oh wait no i do want ah let me do this it's gonna be faster to do it this way plus then you can see it somewhere if i do that outer join now see that i get my city state and zip or null and that may be okay because my result set i really want to see in this because it's from stats that that primary table typically says i want to get all the records the exception would be in a where clause and so i don't want to limit based on my joins then i can do left joins and now what i can do is from there i can do stuff like you know i could do stuff that says um if it's a null then do something else and so i could actually say actually what i want to do with the same thing is say i only want to do it i want to find everything where i don't have a matching address then i could say where city is now uh i'm gonna have to do header.city i'm sure well actually i won't because it's in two places but nevertheless so now i can take that and i can say where city is null and that's gonna show me the cases where i need to get a lookup value set up properly where i don't have things properly matched oh let's take that let's do that like that just to make sure we've got that in our little examples so what i can do here is i can say you know here's all the data but i could actually say hey where is all the situations where i don't have an address well now i can pull that and this would be in some cases it's going to be a a complex type of mapping of data where i don't want to i want to do something if the data exists but i don't if it's if that mapping is not there so i could say here um so i've got my left join oops so with my left join i've got tests but i could say hey for all of these guys that have one i want to get that lookup type so i could even do there i can say now if i do interjoin uh was that lookup type l on l dot what was that l type id equals and if i do that in the actual address which is going to be adder dot type id i believe then here um because i've got an inner join i'm forcing it and because i don't have the record from my left join for that test then i'm still limiting i'm not going to see the test record but i could say hey if i do if i have an address [Music] uh then go ahead and give me a oh i'm sorry i'm not displaying it so let's do let's do this uh so let's do lookuptype.name let's change a little bit and then that's gonna be was it l dot code [Music] oh let's do this and so now i can see the code if it exists but now i have to once i do that enter once i do that left join if i do inner joins then that could if those are related to what i left joined to then i can suddenly run into some issues now the difference is that we can we can do stuff either in inner joins or through a where clause so in order to um let's go back to this prior run so here this one here we're getting everything back but then we're getting rid of that we're saying where inner city is null or i'm sorry address.city is null so now we're you know basically pulling all our records back and then we're reducing the result set in the where clause whereas we could do it without aware you know so because what we really want to do is we want to find like for example we want to flip this so instead of doing where address city is null is so we've got here instead of our where um let's see so this was our left join so if we look at our left join what we can do here we could do that where clause and try to reduce it or we can try to pull it separately so that we're trying to limit the amount of records that come back which case we could say here let's see then what we'll do instead of pulling everything is we go directly to our wear so it's now not going to pull everything and actually we can do this come all the way back here we're going to instead of even doing the join and walking through all of this we want our um where what's that going to be s dot value 1. not in and we can do select type id from look up type [Music] so now if we do that whoop there we go so now we're gonna we're getting our code there and we're saying hey that's where it doesn't exist now here we're doing it with an uh where clause and sometimes that can get pretty complicated because you're going to end up having all of these selects that are out in your where clause and that can be in itself a little bit of a problem because you end up having a whole bunch of interselects within a select so what you may want to do is and so that's uh so you can have that or you can have this left join or you can try to pull it from uh let's see what's another one that's i guess that's really the two ways you normally are going to do it you could do it again you can pull it from a from a table but if you wherever possible you want to avoid doing a full table so this is something where this is going to be moderately costly because it's got this select now usually what's going to happen is it's going to because this is in the where clause this select is going to occur once and it's going to it's going to be able to tune that in some cases it's not in particular depending on where you put your inner selects particularly if you did it here if you did it on the select side the left side of the from instead of the right side of the wear then every record that it hits it's going to trigger that select and then you can have something that blows up exponentially basically because now you've got selects each row each result set triggers yet another select and it can get out of hand pretty quick so typically what you're going to do there particularly if you've got like a compact plex select is instead of doing an inner select is that you can actually create a source so i can do um i can do that and i could do inner join instead of a table i can actually do this i'm just going to call him source and i could say on source because i can only access what i select here so on source.type id equals uh s dot what was that that was going to be oh value one so in this case instead of i can i could actually limit some of these things out i can put a where within here so i could say where uh like type equal where are my types uh we're like you know code equals bus i guess that's all of them in this case um so that's probably not as useful but if those were different ones and i can pull that um then i can actually limit instead of pulling everything from lookup type i can do it across only a couple and do my inner join there if i do it here what we're going to see is the same result we saw before yep except for now i'm just not displaying all that extra crap because here instead of an inner join of lookup type i'm just doing the type id so it returns a little bit less but it doesn't really matter what i would really want to do it'd say like where uh what was that let's say look uh well i can just do it with here so look up type let's go look at those values real quick somewhere in here uh let's just do this look up type uh so let's say where code equals home let's say so now what i'm doing is instead of doing an inner join that's going to do a table scan across that i'm just going to say let's do it where all the home addresses are and we're going to find that you know there's only a couple there oh and i so there were so now we're getting this lowered result set and instead of where i could instead move this out so uh let's see where did i have that before here we go so i could even do here where address that was where i drew a city i can just change it so i can say even with this left join i could say where address [Applause] oh that's not on the lookup uh here we go so i can actually play around with these quite a bit so here uh enter join on that and then i can say where l.code equals home okay so i get those but now also i could enter join i could do a left join on that and i could say hey go ahead and show me all of them [Music] but only if it's home do i want to see something stats left join that on that oh and in this case i'm still going to change this up a little bit because l code is going to be null so i could say or well that code is null and now what i'm going to do is i'm going to find the non-maps or the homes so we can move our wares around quite a bit and that will make a bit of a difference in the performance tuning now we're only dealing with you know a half dozen records or something like that at most so you're not gonna see it but if you have a database even with hundreds of records uh definitely if you get into thousands or tens of thousands or millions then you can see some dramatic changes depending on where you put your where clause and how you do indexes which is something we will refer to we'll get into that a little bit later about how to do some indexing and uh where to make sure that we have indexes or where it may be beneficial to do so i think i'll do it for now i just wanted to play around with those a little bit i don't have complex examples yet in our test data and i will probably find a nice little you know demo database that we can use i may even provide you one so we can get a little deeper in some of our more complex types of queries that being said we'll wrap this one up so go out there have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our tutorials of sql focus on
mysql and
mariadb
and uh we're gonna change pace a little
bit uh we've gone through stored
procedures and stored functions and
looked at those
and now we're going to get
a couple of issues or a couple of
sessions tutorials i guess we're going
to talk a little bit about
uh approach and it has to do with
performance tuning and things like that
to some extent
but also just to
talk through some ways to approach
queries
and where clauses and things of that
nature
this is going to be
it's really more like
not really necessarily suggestions as
much as
options because i think you'll find as
you get into these things that
sometimes
there's certain approaches that you're
going to need at certain times
and
it just that's the way it's going to
work but they're also going to be
situations where you have different
approaches and how you do it
will impact
speed things like that because your
your goal is to
leverage as much as possible
the indexes
and other built-in
pieces of the database that allow you to
quickly
query
and indexes are probably the biggest
thing and then we'll we will talk a
little bit as we get further into us
about some different types of indexes
and
a little bit more on those specifically
but today i really just want to talk
about
we're going to look at inner and outer
joins
and so the first thing i want to do is
we're jumping back into
some of the data that we've seen before
and we had this table
uh stats
and it just
has you know some codes some values i'm
gonna do is i'm going to add another
record in there and the key here is
value one
because what i had before is i had value
one
values were from one to three
now i'm going to add one that's got a
value of six and it's particularly
because
this table is not tied to anything else
and we're going to go back and look at
the lookup type
[Music]
and
based on the ids
we only had numbers one through four
so if we wanted to for some reason you
know if if this was just you know we're
just playing around these tables
if
we wanted to do select star
uh let's say
let's do this
let's do select
code
and well we're gonna do this a little
bit so we're gonna like select code from
uh
stats so
that just gives us those but now what we
want to do is we want to do
uh stats so we're going to alias him as
s
so that's just be the stats so we're
going to do
s dot code
comma l dot
code
and let's say we do an inner join
look up type
l
on l dot
type id
equals s dot value 1.
[Music]
so what we want to do now
is we're going to say okay for those
values in value 1
where what would be the
the lookup type for those
now if we do this with an inner join
[Music]
we're going to get
for each of those we're going to match
but notice for our test record that we
created it doesn't exist down here
because
it's an inner join
it says hey if that value doesn't exist
then it's not part of the result set
now if we wanted that to show up anyways
all we have to do
is we do a left join
[Music]
and now we're going to see
that record does show up
but it has a null value
now what we're getting here
the two different result sets is because
of the inner join
it limits
um records that are coming back from
stats
now that's not always what we want to do
in some cases we're going to want
essentially all of the records to come
back but like in this case let us know
if that value doesn't exist
so we could do this
through foreign key relationships and
things like that we can
force
a value to exist but in some cases it
won't and particularly once you get into
uh comparisons and things like that
you're gonna have like uh and even for
example you may have things like
contacts where you may or may not have
an address record
and maybe you want to go through and say
okay
give me a list of all my contacts if i
have an address i want to see it but if
i don't then
that's okay
so you could show me for example a null
and we can do the same thing um we had
for that
show tables
uh what do we call address okay
let's see what our address values are
oh so we can do the same thing so now
let's take
that same one
and now
instead of lookup type let's do address
and we're going to call it a
uh we'll call it adder
and on
adder.something equals that
and here
uh let's do address name well this is
city
state
oh that's a dot
[Music]
and zip
[Music]
and instead of it being value one we're
going to do it on t1id
[Music]
one id
so now
paste that in
so now what we're doing
is we're saying hey if that links up to
an address let's see it i want to see
the city state and zip for that well
again
we're now seeing that we have some
records that don't
uh that don't show up we had here we had
eight rows now we're down to
oh i'm sorry not eight rows nine rows
when we do a select star
but here
we're gonna get eight so we say oh wait
no i do want
ah let me do this
it's gonna be faster to do it this way
plus then you can see it somewhere
if i do that outer join now see that i
get my city state and zip or null and
that may be okay because my result set i
really want to see in this because it's
from stats that
that primary table
typically says i want to get
all the records
the exception would be in a where clause
and so i don't want to
limit based on my joins then i can do
left joins and now what i can do is from
there i can do stuff like
you know i could do stuff that says
um if it's a null
then do something else
and so i could actually say actually
what i want to do with the same thing is
say i only want to do it i want to find
everything where i don't have
a matching address then i could say
where
city is now
uh i'm gonna have to do header.city i'm
sure well actually i won't because it's
in two places but nevertheless so now i
can take that and i can say where city
is null
and that's gonna show me
the cases where i need to get a lookup
value set up properly where i don't have
things properly matched
oh
let's take that let's do that like that
just to make sure we've got that in our
little examples so
what i can do here is i can say you know
here's all the data but i could actually
say hey where is all the situations
where i don't have an address well now i
can pull that
and this would be
in some cases it's going to be
a
a complex type of mapping of data
where i don't want to
i want to do something if the data
exists
but i don't if it's if that mapping is
not there so i could say here
um
so i've got my left join
oops so with my left join i've got tests
but i could say hey for all of these
guys that have one
i want to
get that lookup type
so i could even do there i can say
now if i do interjoin
uh was that lookup type
l on
l dot
what was that l
type id
equals and if i do that in the actual
address
which is going to be adder dot type id i
believe
then
here
um
because i've got an inner join i'm
forcing it and because i don't have the
record from my left join for that test
then
i'm still limiting i'm not going to see
the test record but i could say hey if i
do
if i have an address
[Music]
uh
then
go ahead and give me a oh i'm sorry i'm
not displaying it so let's do
let's do this
uh so let's do lookuptype.name let's
change a little bit
and then
that's gonna be
was it l
dot code
[Music]
oh let's do this and so now i can see
the code if it exists but now i have to
once i do that enter once i do that left
join if i do inner joins then that could
if those are related to what i left
joined to then i can suddenly run into
some issues
now
the difference is that we can
we can do stuff either in inner joins or
through a where clause
so
in order to
um let's go back to this prior run so
here
this one
here we're getting everything back
but then we're getting rid of that we're
saying where inner city is null
or i'm sorry address.city is null so now
we're
you know basically pulling all our
records back and then we're
reducing the result set in the where
clause whereas
we could do it without aware
you know so
because what we really want to do
is we want to find
like for example
we want to flip this
so instead of doing where address city
is null
is
so we've got
here instead of our where
um let's see so this was our left join
so if we look at our left join
what we can do here we could do that
where clause and try to reduce it or
we can try to
pull it separately
so that we're trying to limit the amount
of records that come back which case
we could say here
let's see
then what we'll do instead of pulling
everything
is we go directly to our wear
so it's now not going to pull everything
and actually we can do this
come all the way back here
we're going to instead of even doing the
join and walking through all of this
we want our um
where
what's that going to be s dot value 1.
not in
and we can do select
type id
from look up type
[Music]
so now if we do that
whoop
there we go
so now we're gonna we're getting our
code there and we're saying hey that's
where it doesn't exist now here we're
doing it with an uh where clause
and sometimes that can get pretty
complicated because you're going to end
up having all of these selects
that are out in your where clause
and that can be
in itself a little bit of a problem
because you end up having a whole bunch
of interselects within a select so what
you may want to do
is and so that's uh so you can have that
or
you can have this left join
or
you can try to pull it from
uh let's see what's another one that's i
guess that's really the two ways you
normally are going to do it
you could do it again you can pull it
from a
from a table but if you wherever
possible you want to avoid doing
a full table
so this is something where this is going
to be moderately costly because it's got
this select
now usually what's going to happen is
it's going to because this is in the
where clause
this select is going to occur once
and it's going to it's going to be able
to
tune that in some cases it's not
in particular depending on where you put
your inner selects
particularly if you did it here if you
did it on the select side the left side
of the from instead of the right side of
the wear
then
every
record that it hits it's going to
trigger that select
and then you can have something that
blows up
exponentially basically because now
you've got selects each row each result
set triggers yet another select
and it can get out of hand pretty quick
so typically what you're going to do
there
particularly if you've got like a
compact plex select is instead of doing
an inner select
is that you can actually create a source
so i can do
um
i can do that
and i could do inner join
instead of a table i can actually do
this i'm just going to call him source
and i could say on source
because i can only access what i select
here so on source.type id
equals uh
s dot what was that that was going to be
oh value one
so in this case instead of i can i could
actually limit some of these things out
i can put a where within here so i could
say where uh like type equal where are
my types
uh we're like you know code equals
bus
i guess that's all of them in this case
um
so that's probably not as useful but if
those were different ones
and i can pull that
um then i can actually limit
instead of pulling everything from
lookup type i can do it across only a
couple and do my inner join there
if i do it here what we're going to see
is the same result we saw before yep
except for now i'm just not displaying
all that extra crap
because here instead of
an inner join of lookup type
i'm just doing the type id so it returns
a little bit less but it doesn't really
matter what i would really want to do
it'd say like where
uh
what was that
let's say
look uh well i can just do it with here
so look up type
let's go look at those values real quick
somewhere in here
uh
let's just do this
look up type
uh so let's say where code equals home
let's say
so now what i'm doing
is instead of doing an inner join that's
going to do a table scan across that i'm
just going to say
let's do it where all the home addresses
are and we're going to find that you
know there's only a couple there oh and
i so there were so now we're getting
this lowered result set
and instead of
where i could instead
move this out
so
uh
let's see where did i have that before
here we go
so i could even do here where address
that was where i drew a city
i can just change it so i can say even
with this left join
i could say where
address
[Applause]
oh that's not on the lookup
uh
here we go
so i can actually play around with these
quite a bit
so here
uh enter join on that
and then i can say
where l.code
equals home
okay so i get those but now also i could
enter join i could do a left join on
that and i could say hey go ahead and
show me all of them
[Music]
but
only
if it's home do i want to see something
stats left join that on that
oh and in this case
i'm still going to change this up a
little bit because
l code is going to be null so i could
say or
well that code is null
and now what i'm going to do is i'm
going to find
the non-maps
or the homes
so we can move our wares around quite a
bit
and that will make
a bit of a difference in the performance
tuning now we're only dealing with you
know a half dozen records or something
like that at most so you're not gonna
see it but if you have a database even
with hundreds of records uh definitely
if you get into thousands or tens of
thousands or millions then you can see
some dramatic changes depending on where
you put your where clause
and
how you do
indexes which is something we will refer
to we'll get into that a little bit
later about how to do some indexing
and uh where to make sure that we have
indexes or
where it may be beneficial to do so
i think i'll do it for now i just wanted
to play around with those a little bit i
don't have complex examples yet
in our test data
and i will probably
find a nice little you know demo
database that we can use i may even
provide you one
so we can get a little deeper in some of
our
more complex types of queries that being
said we'll wrap this one up so go out
there have yourself a great day a great
week and we will talk to you
next time
you