Relational Algebra- fullouterjoin

Question

The “left outer join” operation is an extension of the relational join operation. The basic idea is that all tuples in the left relation always appear in the “left outer join” result, irrespective of whether they join with any tuple in the right relation. For example, consider two relations R(A, B), and S(B, C). Assume that R has the following tuples: (1, 10), (1, 20), and S has the following tuples: (10, 75), (10, 85), (30, 95). R LeftOuterJoin S will have the following tuples: (1, 10, 75), (1, 10, 85), (1, 20, null). Note that the first two tuples are the result of a regular join operation between R and S. The third tuple (1, 20, null) is added to the left outer join result because the tuple (1, 20) in R (which is the left relation) does not join with any tuple in S; the S column values are set to null in this case.

Note that the left outer join operation is not symmetric because tuples in the right relation do not appear in the outer join result unless they join with a tuple in the left relation. In our example, the tuple (30, 95) in S (the right relation) does not appear in the left outer join result because it does not join with any tuple in R.

The RightOuterJoin is defined in a similar way except all tuples in S will show up in the final result when we apply R RightOuterJoin S. In addition, we also define the full outer join between two tables as :

R fullouterjoin S = (R Leftouterjoin S) ∪ (R Rightouterjoin S)

Given the above description of full outer join and assume T = R fullouterjoin S under an arbitrary join condition, answer the following questions.

Therefore, T = R fullouterjoin S = (R Leftouterjoin S) ∪ (R Rightouterjoin S)

• How can you recover the exact operands R and S from T? If yes, what is the function f? If not, why?

-> after searching online, i think the answer is that we can't. Can someone verify

Answer 1

I don't know the Hopkins book, so what does it say for Leftouterjoin where there's a tuple on the left with no match on the right? For example (1, 20) in R.

I expect it'll say that for attribute C there will be Null.

So this gives us a way to recover the original content of the operands:

• If C is Null, the A, B must be in R only.
• If A is Null, the B, C must be in S only.
• If all attributes are non-Null, the A, B must be in R and the B, C must be in S.

Does the Hopkins text give a way to detect if an attribute is Null? There is no 'standard' Relational Algebra when it comes to detecting Null; indeed most variants of RA don't support Null at all, for a very good reason ...

Except: I'm making a big assumption, which is probably not valid. Are Nulls allowed in the base relations R, S? Then a Null in attribute C of the fullouterjoin might be because there was a tuple in R that didn't match to S, as I described above. Or might be because there there's a tuple in S with attribute C as Null.

This is just a small example of the logical incoherence in allowing Nulls. Don't do that; don't use outer joins; don't expect that you can recover the content of the operands from the result of an outer join. After all, most operators in logic or arithmetic lose information, why expect outer joins to be special?

A bigger incoherence is if there's tuples with attribute B as Null -- either in R or in S or in both. What does the Hopkins book say happens with outer joins? Does that behaviour make any sense to you?