What can you do when you have multiple tables?

When you have multiple tables that have the same field basically so take it from here, not from another table that’s correct if you have multiple tables with the same name you must fully qualify them the column name because Oracle doesn’t know which table you’re going to get it from its catcher thank you the exception to that rule is if it’s in the user because you can’t fully qualify it that’s why we took it off job ID and amp deploy EID even though they’re in both tables since we’ve sender using it’s the same value in both tables and they will not let you fully qualify it the others you can now let’s say the last name let’s go back to the previous table previous slide the last name was in the first table the last name was not in the job history table okay it’s only in one table since it’s only in one table I did not have to fully qualify the last name see that I could have just said last name and they didn’t have to say em that last name but it’s only in one of the tables.


And Oracle would have figured out which table it’s in all right so if it’s not if it’s not an if it’s unique to only one of the tables in your join you do not have to fully qualify it, but good programming practice says always fully qualify those names why is it not a good idea to leave off the full qualification I’ll give you a hint that has something to do with maintenance of your application if I leave off this M that last name and I just said last name this would work today what might happen tomorrow that would make this not work if another feel from that table you’re joining decides to create another column that’s his last name it error out saying that it didn’t know which one it was specifying perfect that’s exactly it exactly it so we don’t have a last name column in both tables so we write it if we wrote it without the amp taught it would work today but if someone came along and had to modify that second table and aided a column called last name and you had this code all over the place.

It would be broken all over the place you’d have to go in and fully qualify it, so my point is the good programming practice the golden rule is anytime you reference more than one table in a query be it one be it a join or even a sub-query a query within a query always fully qualify all your columns and then you can never run into that problem with the following exception you can’t qualify the columns in the juicing clause and you can’t qualify the columns in the in the natural join other than that qualify qualify all your columns well I don’t like that you know I don’t like that I prefer to qualify every column and the only way you can fully qualify every column is to use the join with the on clause because but join with a non clause there’s no rule that you cannot qualify these please columns used in the joint there’s no rule like that so that join with the on clause you can fully qualify everything and you’ll never run into an error you would never run into you know the kind of error where you qualified.

Something by mistake well you didn’t qualify something by mistake you just qualify everything and it always works, so that’s why my personal opinion is always used to join with the owns a little more typing but it’s better for maintenance of your application so easy to qualify using table aliases we went through this example here we went through the natural joint example we showed one matching we went through the using clause with two things and since it didn’t match on all three it was okay I fully qualified the columns that we’re not in the using Clause the columns and using because I did not qualify okay and this is just a summary of what we talked about before so it’s in the book on page 3 28 if you’re confused about when to use one when to the news the other when you have to qualify that’s all specified in here’s your non equally joins page 3 30 you almost always use the on clause with non รจ qui joins you can use the using but it’s very rare.

So you almost always use the yarn and it’s where this column is less than that column wait a minute you yeah where this column is less than greater than less than or equal greater or equal or where this column is between these two values between this value in this column and that value in that column, so those are the syntax is to hardly ever use so I’m not going to spend any very much time on it here there was one other kind of join that we didn’t talk about called the self join anyone ever do a self join anybody self joins it yourself now I noticed from before David you had your hand up I assume that that was from an old situation if there isn’t let me know okay a self joint is where you have a table that has basically built-in hierarchical data meaning that one column in the table points to a value in another column in a table like a parent-child relationship let’s look at an example of a self joint here I have a table called family.

It’s got ID name mother ID and father ID the mother ID is a to that points to the ID in the same table number two which is Sabina so nurses mother is number two Sunita and nurses father is number one Harry whereas I Pat’s well Pat doesn’t have a mother or father Kiara has a mother of number 16 and that’s Pat’s Pat’s is Kiara his mother and number 15 nurse is he on his father, so that’s all great now what is this useful for let’s say I want to show the child Kiara and fats as the mother and nourish as the father so I want to show every child and who the mother and father are but I don’t want to just show the numbers of the mother and father I want to show the names well this is typical joins where you have a department number in the employee table but you don’t want to show the department number you want to show a department name and that’s in the department table you have to join the two tables well all the information is in the same table the family table.


So I’m going to select my I’m going to have three tables here family f1 family f2 and family f3 okay f1 is going to be let’s see f2 is going to be the one with the let me see if I got this straight, yeah the child is going to be f2 name so I’m going to join f1 to f2 and the matching column is the mother ID so if I’m on the child if child the child table is f2 then I have a child Kiara and the mother ID has to match f1 that ID so f 2 dot mother f 2 dot mother ID so for let’s see for a child of Kiara f2 mother ID if mother ID is 16 and that therefore it will display the mother and father properly but notice we have to join the table to itself so it’s from family f1 join family f2 on the f2s mother ID is equal to the f1 ID, of course, f2 is the child and then we rejoin that to f3 on the F 2 dot fathers ID equal f3 ID because the f3 is the father and that’s why we named f1 name the mother F to name the father and f3 that name of the Father and f2 name the child that is a self join.

That’s an example a good example of where we would do a self joint where we’re showing relationships where one column in the table points to a different row in another column in the same table and we want to match them up so it’s just like a join but we just happen to mention the same table more than once any questions on that the hardest thing that most people find when they do these joints is to figure out which is is is f1 the mother is that the father is the child you know you got to spend some time thinking about this ok I’m going to display f2 net at that name is the child, so there is a child f2 name is a child and then in f2 and f to the mother ID has to match f1 the ID so f1 has got to be the mother and enough to the Father ID has to match f.3d update f3 ID so f 3 has to be the dad takes a little thinking at thinking about it looking at it to figure out exactly how to code this and sometimes the first time you do what you’ll get it wrong but you got the cut the key is the concept here.

A self join questions on that ok inner join vs outer join ok we talked about inner and outer left-right and full the on syntax is select from table left outer join table2 with a non and you tell it what to match up barely standard you could leave outer join right outer join or full outer join keyword outer is actually optional stay left full or right it knows exactly what you want to do it knows its outer just like an inner join the keyword inner is optional you can just say enjoying Cartesian product you have to actually say cross join to make it a Cartesian product so whereas the old syntax remember if you left out to remember if you have four tables you need to rejoin pieces of logic but if you forget to join the first two tables to the third it does a cross join and that’s because of a mistake well here you can’t do a course joined by mistake because you have to specifically state cross join.

So that’s another reason for the new syntax you’re not going to get cross joins by mistake well actually there is a slight way they’re always to do it but you’re not going to do it very often the other way people would get cross joints went to stake all the time because they forget to join one of the tables in okay.

Leave a Comment