Even if youre doing the same left to right order

This preview shows page 46 - 53 out of 53 pages.

operation on b and c and then combine the result with a. Even if you're doing the same left to right order. Associativity is also very common to cross product and the natural join and so on are associative . What's interesting is the outer join is not associative . And I'm going to demonstrate that, not with the college database but with a very simple set of tables, each with one tuple, that I'm going to set up specifically to make my point. So I'm going to create three tables, each with two attributes, and put just one tuple in each one. So we've got, table T1 with attributes A and B and tuple 1, 2. We've got T2 with attributes B and C and a tuple 2, 3 that matches the T1 tuple. And then table T3 has attributes A and C. So attribute A from table T1 are matching T1. And C from T2, but the values don't match at all. I would highly recommend you pull out a pencil and paper and actually write these tables and tuples down so
Image of page 46

Subscribe to view the full document.

you can see what's happening. Now let's look at this question of associativity. So we're going to do the natural full outer join of the three tables and we're going to start by putting the parenthesis on the left. So we're first going to do the outer join of T1 and T2. And then do the outer join of that result with T3. And let's run the re, query, and we see that we got two tuples 1, 2, 3, and 4 null 5. We got 1, 2, 3, because T1 and T2 had that nice pair of tuples that combine with each other.But then when we took that result and did the outer join with T3, that gave us an unmatching tuple in T3. So we got four and five with a null value in B. Now let's try the other associativity. Let's just change our parentheses so that T2 is outer joined with T3 first and then the result of that is outer joined with T1. We run the query, and we get a different result. And that's because we started with our unmatching tuples which then didn't match correctly with T1. Again, the best way to do this is to take your pencil and paper and look at what's happening or try running the queries yourself. To see that the outer jo, join is not associative. And by the way the left outer join and the right outer join are not associative either. So none of the outer joins are associative. The full outer join, however, is communicative while the others aren't.\ Ok, so why is this important? It's important because if you do queries that do have outer joins of more than one relation, then it is important to think about the order. It's not a performance thing, it's actually what result you get may be different with different orders and parentheses, parenthesizations of your outer joins. So, in conclusion the family of join operators is used in SQL to combine relations where we're explicitly doing operations like relation, relational algebra. The inner join is the relational algebra theta join, where we explicitly combine relations on a specific condition. We have a natural join operation which implicitly combine, equates column, the values in columns that have the same name and eliminates those duplicate columns. Both of those, can be expressed in fairly similar ways without the join operator. The outer join, however, is sort of significantly different because it does add the tuples that don't match the join condition. And pads them with null values and it, it's quite useful. People use join operators quite a lot. The one thing I mentioned is that join operators can be used to kind of tell the query processor how it should execute the query and that can sometimes be used to improve the performance of queries, of equivalent queries. Though in theory, again, database systems should always find the best way to execute their queries, but that's a tough thing to do. And so people do sometimes give hints in the way they formulate their queries sometimes using the join family of operators to do that. Example table:
Image of page 47
Table 1:
Image of page 48

Subscribe to view the full document.

Table 2:
Image of page 49
Table 3:
Image of page 50

Subscribe to view the full document.

Lets take a look at associativity
Image of page 51
Image of page 52

Subscribe to view the full document.

Image of page 53
  • Fall '13
  • Relational model, Join, Composition of relations, Outer Join

{[ snackBarMessage ]}

Get FREE access by uploading your study materials

Upload your study materials now and get free access to over 25 million documents.

Upload now for FREE access Or pay now for instant access
Christopher Reinemann
"Before using Course Hero my grade was at 78%. By the end of the semester my grade was at 90%. I could not have done it without all the class material I found."
— Christopher R., University of Rhode Island '15, Course Hero Intern

Ask a question for free

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern