MySQL Database Tutorial - 23 - Outer Joins Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what is going on guys welcome to your
23rd - ql tutorial and in this lesson
well actually before we begin I want to
mention this I updated the MySQL files
so if you guys want that new table go
ahead download the file you can get it
now there you go and if you don't know
it's a my website is T&B forum calm and
just go in the database section it's
right there for you guys but anyways
what I want to talk about in this
tutorial is outer joins but before I
begin that I want to mention something
that I probably should have mentioned
last time remember when we were talking
about column names and how you can have
a column and you can give a nickname by
writing like as C or something and then
you can refer to that column as C well
you can also give tables nicknames so I
just wanted to show you guys a quick
example because I'm probably gonna be
giving tables and nicknames in upcoming
tutorials and I didn't want you you
didn't want you guys to get confused so
let me go ahead and type the whole query
because for these it's easier if I type
the entire query and then explain it so
I'm just gonna go ahead and select I got
seller ID and I'll give you guys a
couple I dot name and seed ID I might as
well talk about difference from
customers as C so you guys can probably
see what's going on already items as I
so basically what we did is just go
ahead and finish this before I start
blabbing on seller underscore ID equals
and you see ID now let me run this and
make sure this makes sense there we go
no air is good so basically like I said
not only can we use the as keyword to
give a column a nickname but we can also
give a table a nickname so whenever we
do that just go ahead and write your
table name as and then give it a
nickname
hopefully shorter because you know if
it's longer it's not really a nickname
and do that with any table you want and
then on your entire query instead of
referring to this as items seller ID
items name customers ID you just go
ahead and you use that nickname
so not only whenever your from referring
to the columns but also in your where
clause
as well so again like I said basically
you can give tables nicknames as well as
columns nicknames and it just makes your
coding a little bit faster maybe a
little bit easier to read many ways I'm
gonna be giving nicknames in the
upcoming tutorials just didn't want you
guys to get confused but now we can get
to the good stuff the heart of the Soria
which is outer joins so what is that
outer join well let's go ahead and
remind ourselves what an inner join was
an inner join was basically when we have
two columns and we want to match them
together okay that's nice but sometimes
and you guys probably noticed this
already you're gonna have these
instances where your rows don't line up
exactly maybe you have some items that
don't have a cellar ID or maybe you have
some customers or users for your website
who aren't listing items yet well that's
nice but what if our boss comes in and
he's like Bucky listen I'm gonna need a
list of all the customers and the items
that they're selling for example if
Bucky's selling a tuna fish sandwich and
a Corvette and a life vest I'm gonna
need a list of those so again the name
of the user and the items are selling
okay simple enough we'll just do a
simple joint should be on your way then
he's like oh by the way I also need to
know I also need the name of the user
even if they aren't selling any items
and we're like okay that might get
confusing so let's go ahead and try to
tackle this problem so we go ahead and
select customers dot name which is
basically like the username in items dot
name so basically the username in the
items that they're selling from
customers and items where remember where
is basically saying how are these tables
related so customers
customers dots ID should equal seller
underscore ID so we go ahead and run
this go check it out and we'll just go
ahead and sort it make it look a little
bit prettier we hand this to our boss
and he's like wow this is looking pretty
good
wait a minute I could have sworn that
there are some more users on our website
what the heck happened to them some
people's names are messing up well the
reason that some people's names aren't
showing up is because they don't have
any items listed right now but what if
we wanted to show them anyways okay that
could get kind of confusing so let's go
ahead and rework this query a little bit
so of course we want the customers in
the items name and we also want that we
also want to use the customers table but
instead of just going customers and
items where like we did before
what we want to do is write list left
outer join and I know the syntax looks
weird so I'm just gonna go ahead and
type this query and then tell you guys
so left outer join items so take note
that there's a table name here and then
you're right basically join with this
table on and remember the on keyword is
basically saying how are these tables
related so customers dot ID in this is
basically the same condition is less
time equal seller underscore ID so let's
go ahead and run this and see what we
get well check it out not only did we
get basically the same list as last time
but as you can see it now lists the
users that do not have any items listed
as well so when they don't have anything
to associate it with when your
conditions aren't met and they don't
have any related data it still shows the
table on the left so basically this
query is different from a simple joint
because whenever you hit left outer join
it's basically saying that show all
customers with items even if they aren't
selling any items sounds weird and the
keyword left what this means is you know
how that look at this syntax right here
you see that right there
there's a table on the left in I know
it's kind of like below it right now but
if this is one long line then this would
be on the right so customers is on the
left and items is on the right of the
syntax right here whenever you use the
keyword left you're saying left means to
include all the rows from the table on
the left
therefore the customers table no matter
what so include all the customers no
matter what no matter if they have items
or not if we were to go ahead and hit
write right here then what it would do
is it would include all of the items no
matter if they had a seller going with
them or not so let's go ahead and take a
look at that so as you can see now all
of the items are listed but say this
user went ahead and they deleted their
user name and they got banned or
something but the item was still on the
website well even though they wouldn't
have a user associated with that item
since we use that right outer join it
takes all of the rows from the table on
the right which is in this case all of
the items and it forces those to be
shown so even though it doesn't have a
customer associated with it
it forces those items to be shown so
that is the difference between the left
outer join and right outer join
basically do you want to force these
rows to be shown or these rows to be
shown so again that is what an outer
join is basically forcing rows to be
joined or excuse me forcing rows to be
shown different from my regular join
where both of the basically they have to
have related data so there you go I'm
gonna be done talking hopefully you guys
understand and don't forget to download
the files go to my website tnb form com
and they're right there so thank you
guys for watching don't forget subscribe
and I'll see you guys in the next lesson