join

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