SQL Three Table Join Examples

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

Channel: Brian Finnegan

Views: 71,014

Rating: 4.8141413 out of 5

Keywords: bis235, database, mysql, SQL, joins, Join, Table

Id: wJmBZGwRNzU

Length: 14min 12sec (852 seconds)

Published: Fri Apr 18 2014