How to join
3 Table
SQL Three Table Join Examples
Sharing buttons:
hey there a student recently pointed out
to me that while I asked you all to do
table joins involving three or more
tables
I do not specifically cover that in the
videos or the tutorials so fair's fair I
ought to do something to fix that and so
here we are so I want to run through in
this example a joint
involving three tables basically you
know spoiler alert the bottom line is
you repeat the process to join two
tables only you do it two times if you
were joining four tables you would do it
three times
basically if you're joining n no matter
how large n is you will have n minus 1
joints to consider okay so quick rundown
of situation I'm going to use the
employees database that served as the
example for the sequel tutorials ok so
safe sake of argument we are interested
in listing all the current employees in
the finance department ok that is
interestingly from a design perspective
going to involve 3 tables and I'll try
not to get too hung up in the ER
diagramming implications although that's
point in the course where we're looking
back let's look at the tables involved
ok so first if we're looking at the
employees in the department table or if
we're looking to find the employee names
for the department let's talk about the
department because we will need the
department name because we know the
department name is finance that we're
interested in looking at so in the
department table low and behold here's
department nade name we'll need that
alright now let's look at the employee
table I'd be faster just to type
employee
okay so employee table has all the
information about the employee first
name last name gender hire date etc
we're clearly going to need this
information because we want the
employees first and last names for the
department that equals department name
finance ok notice here though there's no
commonality here there's no link which
you would expect under a certain set of
circumstances however we said we want
the current employees which means
that there's a time element which means
that we need to involve a third table
and that table is called depth amp and
basically that is the relationship table
that resulted from the fact that
department and employee is a
many-to-many relationship here because
we need to capture that time element so
if we look at depth amp we will find
that there is an employee number points
back to the employee table there is a
department number points back to the
department table and from and to date
okay so we have all of the conditions
that we need let's actually do the join
here so we're going to select first name
and loop and last name right that's the
information we actually want and we will
be getting first name and last name from
the employee table we see right here so
we know one of the tables is going to be
employee we also are going to have a
wear condition involving department name
so we know we need to involve department
as well and we know that we are going to
need to join the two using these two
foreign keys here in department employee
so we will also need to involve that as
well we also have a condition an
explicit condition in department
employee here we need the to date to be
null to date to null means that their
time of active employment in the given
department has not yet ended so if
that's empty it means they are still
there which is what we are in this
example looking for although we don't
necessarily need that for the three
table example that's useful real-world
real-world context there okay so we have
and I'm going to move this bump this up
just a smidge if I can which I'm not
sure I'm going to be able to no not not
terribly easy you're just going to have
to look off the bottom of your screen
I know what I can do I've to stretch it
up like this
you can see a little of that okay that
should be a little bit easier to see
okay so there's our three tables three
tables to join conditions where we so
first up so we're joined conditions
first explicit condition second so we
need to enjoy join employee and the
department employee table where employee
and there's three there's three or more
techniques I'll run through two of them
you can extrapolate from there first the
easiest but the least efficient the kind
of old-school
setting the values explicitly equal
rather than using joint so we want
employees amp number two equal
department employees amp number that
will join from employee to Department
depth M okay we also need to join depth
amp to Department so and department dot
step number is equal to depth amp dot
depth number
okay three tables to join conditions one
the employee tables the employee numbers
need to be the same to the department
numbers need to be the same okay however
are we done with our wear conditions no
we also need to not lose track of the
things that we explicitly want what do
we explicitly want we want the finance
department so the department name needs
to be equal finance and depth name is
equal to finance that's one of our
exclusive conditions and we want current
employees only so to date is null that
will give us current employees all right
so with that we should get the current
employees in the finance department now
of course I didn't test this ahead of
time so we'll see if I get it right the
first time
oh and lo and behold indeed I can there
are currently we have a very
international finance department and
there are currently 50 employees in that
department so you need an explicit link
from a path that you can draw
continuously through from the first
table you need to involve straight
straight through to the last table so we
need to join table 1 to table 2 and
table 2 to table 3 and that way we can
get any information from any of those
tables that we need meaningfully
together again to answer the questions
that we have that involve those three
tables in our case yeah employees work
for department they have name work for
here that well I have named work for
here Department underscore employee to
make very explicit that it is the table
that joins departments and employees so
we join employees to department
employees be joined department to
department employees we can reference
departments we can reference we can
reference employees and we can answer
the question that
we have now if we had four you would
just have three join conditions instead
of two in this example okay so there it
is four are in my estimation easiest to
understand but slowest performing in
some instances example let's go ahead
and run through the same example with
the joint syntax as well okay second
option which we can only use because we
have the advantage of the primary key
and the foreign key both in the example
of employee to depth M and from depth to
depth amp being the same in both tables
so employee
it's an amp no with an underscore
between in depth M it's empty the
underscore in between ditto for
Department and depth amp Department
underscore no so since that attribute is
named exactly the same thing which by
the way it does not need to be
necessarily but because it is in this
case we can take advantage of the using
syntax so let's do that because I think
it makes things a little clearer and
easier to follow okay so we see here
we're selecting the same attributes and
with any luck we'll also get the same
results again this is live okay so we're
taken st. where same tables but the
syntax is different so from employee
will say join depth amp using and what
does employee and depth amp have in
common they have amp no in common if
that if one was amp underscored no and
the other was amp underscore n um or amp
pound sign this would not work by the
way okay so there's the first join and
we join the first join to the second
join so join
so we're joining those two two
department and that join will be using
and using ticks friends depth no because
that is the common attribute primary key
to foreign key from department to depth
M okay there we have our join done so
let's add our explicit conditions with
these listed conditions it still goes
and we're so there's a lot of syntax to
keep track of here as well as a couple
of concepts where again the department
name needs to be financed because that's
the department that we're interested in
and we want current employees so to date
we need to be know okay same exact
result let's let's see if it works and
indeed it does we get the same results
we get the same folks mark he'll longer
the whole deal
okay so feel free to stop here but for
the sake of completeness let me run
through the equivalent alternative to
using if you have different attribute
names which we do not so we don't need
to go through this work but I want to
show it to you just so you can see it
feel free to stop here if you're good to
go but give me a second and let's run
through the one last option that I want
to show you okay a lot more words here
because we're not taking advantage of
that matching which actually makes us do
more but let's see it for the sake of
completeness so same attributes select
first name last name where we're
selecting them from let's start with
employee again join same as before only
use the on syntax and since the
attribute from a I'm sorry employee join
depth M
yep underscore em on instead of using so
we have to specify the combination so
it's going to be employee dot M number
must equal depth amp tables amp number
so a lot of words okay so there's our
first join will join that join to our
second joint involving Department on
department dot depth no is equal to
depth m dot department number okay
there's our join still have to do our
explicit conditions with the where and
where the department name is oops
department name is equal to finance and
to date is blank is null so that we have
only the current employees if we're
doing some sort of some sort of alumni
registry where we want everyone who's
ever worked in finance that would be a
different story and we'd leave this
condition out but that's not the
question that we set this example at
would so we need this as well and if
we're lucky we will see yes indeed the
exact same set of people 50 rows now
notice in this case all three queries
took zero seconds so there's no obvious
performance difference but using the the
joint syntax is advisable although in
the for the purposes of this class I
think probably the easiest syntax to
understand if you're a new student is
setting the values equal specifically as
part of the where conditions without
using join if that works better for you
that's fine keep in mind that that may
have negative performance implications
outside of that I hope this was helpful
sorry I had admitted it omit adhere to
for study hardware we're bearing down on
the end of the class the final exam
would be here before you know it so work
hard and I will see you on
![how to join Multiple table in mysql [ Inner Join , Left Join, Right Join ]](https://img.youtube.com/vi/lKPQ94VjySI/mqdefault.jpg)
