The first one select region name from region’s natural join countries where country name equal Canada so it’s just for the country name of coal Canada it’s going to do a natural join and it’s going to do it only on matching column names here’s a join from countries natural join our regions where region am equal America’s here’s another different join these are both natural joints here is an inner join from regions join countries no word natural can say in or if you want using region so it’s going to use only the region column which if that was the only one that matched I get the same an of America’s and America’s here’s a non clause where countries that region ID or regions that region I did use region ID same thing as this gets me the same results up here ok, so those are your typical joins in taxes let’s look at if we have other examples here of anything I wanted to show you.
We’d look at you see any example can you see an example similar to what John Wayland talked about data join on the subset of the second field I know I don’t see I don’t see one of those in here but I’ll put one together for you while you guys are doing AL Arab up with that together for you okay here’s a multi join let’s look at page 329 here’s the join from employees e jointed departments d with a non-then take that result join it to look locations l with an on all of these uses an arm this is very nice coating technique well pretty nice anyway they fully qualified it with the on you can fully qualify everything region name see country name l city okay we got all of this we fully qualified all these from employee on department the joint departments the with a non d department number equal a department ID will eat that department I did and salary greater than 12,000 so here you can join it on a condition with an end and this is not fully qualified they should have fully qualified the salary.
Whatever table the salary was in it would have been better to fully qualify it that’s the only thing in here that’s not fully qualified and probably should have been Jeff is it faster for processing to have the and salary greater than 12,000 in the on join as opposed to in a where statement below hmm good question because sometimes we’ll have a lot of of those things for final query logic for example for final logic for our claims and I’m just wondering if we’re using for pulling a large data set would it really make it go noticeably faster I guess I would think that if anything it would be faster if you had the salary some outside of the joint logic because that would eliminate all the rows in the appropriate table that had a salary that was less than or equal to 12,000 wouldn’t even try to join them but I’m not sure that it makes much of a difference i I don’t know we would have to test that you know it’s funny whenever.
When someone asked me ever I’m asked which is more efficient this or that okay if you go to tutoring classes the answer is always the same when someone asks which is more efficient than the answer is always it depends on a situation and the only way to be sure is testing now I can tell you in general when some things are going to be faster than others but it’s always a matter of testing and this one I’m not sure one would have to test all right and by a good programming practice and I think a reasonable approach is coated the way its simplest to maintain and if it doesn’t run fast enough then worry about making it faster but always modest in the code it in the simplest way if I were coding this in the simplest way I would have taken the salary out and just put it in a regular where clause plus it confuses the issue in here and if that ran fast enough I’m done if it doesn’t run fast enough then I might change it and see if that helps.
I don’t know that doesn’t give you a complete answer but it kind of gives you an approach maybe thank you, okay okay let’s see what else oh yeah non è qui joins the next page 3330 here’s an example from employees he joined jobs j on two times the e salary is less than the j max salary well in the jobs table there’s a max salary for that job position and i want everyone we are twice of their twice the salary is less than the mac celery for the job position okay so here’s a case where you might do a non è qui join something like that alright, so there is playable question yes um so where they’re not equal would it then do a cross join like how is it joining them then when they’re not or when it’s greater than our last time on this one uh how is it joining them it’s doing a non è qui joint it’s not a cross joint it’s similar to a cross joint because it will join each row with every row that matches that condition okay.
That’s what I was thinking thank you yeah not a cross joins each row with every other row there is no condition this joined, so this is really truly a regular join but instead of the matching addition being where they’re equal the mashing condition is where it’s less than okay you often do something like that where you want to find out the every manager who is making less than all the people and then anyone in their in their Department so if you’re managing someone you want to see here’s the manager salary and here’s Jeff salary and the manager is making Lawson is a manager salary and here’s Jim salary so you would have that match wits with multiple of the roads which is typically what a join do you know it matches the department with all the employees in a department this matches an employee with all of the jobs where the employee is making less than two times the employee is less than the maxillary for the job okay let’s see we have any others’ oh yeah I think we want to look at a couple of outer joins 339. I’m selecting job ID from job history and the employee table I’m selecting the job ID and I do a right outer join on employees so I want all the employees even if there are no records in a job history okay so what I’m getting job history happens to be a table with all the previous jobs that employee had in your company and these are all the people that have had a position but they didn’t have any position beforehand so right outer join gets everything from the right table the employee’s table which is this table here and if there are no matching columns from the job history table it just fills it with nulls that are the default, so that’s your right outer join and right means the table to the right of the keyword right out of joints you get all the jobs and employs all the rows from the employee’s table and that’s all of these if it was a left outer join it would have all of these rows.
And if there were no matching them put no matching rows from the employee’s table would have a null on the right side let’s see what else we have here been a full outer join this is full outer join, so that gets all departments with no employees and old employees with no departments is an employee with no Department of fills with now and here’s departments with no employees it fills those with no okay I think that covers most of it yep okay any questions before we go into lab okay chapter 7 we have a lab here again we’re going to use the HR schema and we’ve got parts here and you’re just going to do Part A and then raise your hand if you’re when you’re done and if you have time again do the lab at the end of the chapter yes on Friday year we’re going to cover sub-queries sub-queries minute the main of the chapter were covering on Friday of subqueries I’ve been told that manipulating data is something you don’t do a lot.
So we’re going to just briefly cover that chapter 10 insert update delete chapter 11 created manage tables that you do that a little where you just create a couple of simple tables but those generally done by other people and other schema objects, so my question is let’s see this is seven there’s really only one major table is that correct or do we want to spend more time going over going over some of those other things because we will have enough time to go over subqueries in detail but we won’t have enough time going over insert update delete creating tables and other schema objects.