Full Outer Join

Question

Why is the minimum number of tuples in full outer join equal to max (m,n) ?

Comments

one table has 1 tupple and second table has 100 tupple then full outer join gives atleast 100 result i.e. case where no match b/w tables.

---

ok! thanks

---

@anu Wouldn't we have 101 tuples in that case, as full outer join returns 

maximum of m + n tuples (no matching)

minumum of m + n - the number of tuples matching for the condition given

In your example if that one row would be matchng with other column then we can have 100 tuples in result. Correct me if am wrong.

Ref: https://www.w3schools.com/sql/sql_join_full.asp

---

@anu sir see this question

https://gateoverflow.in/16082/why-is-the-maximum-number-of-tuples-in-full-outer-join-equal-to

Is the selected answer correct there?

Minimum number of tuples=max(m,n)

Maximum number of tuples= m+n (when nothing matches)

Is this conclusion correct?

Answer 1

If nothing matches while applying full outer join the resultant would be (m+n).

Firstly, there is a misconception that min. would occur when nothing would match.

Min. would occur when values in tuples actually matches! And, then  it would be max.(m,n).

E.g.

Consider relation A with 3 tuples and B with 5 tpl.

So, misconception says min of outer join would be if there's no match. i.e m+n = 5+3 = 8 tuples. but this is not the case to get minimum.
To get min. in full outer join:

Consider, 3 out of 3 in A matches with B's tuples

 this time, resultant relation would have: 3(matched) + 2(unmatched) tuple = 5 tuples. (which is less than 8)

This is the min. case, when EVERY tuple in one of the relational table matches with tuples in other. Hence, max of (m,n).

I hope this explanation helps.

Answer 2

If in R and S all attributes are common only then, minimum number of tuples in full outer join equal to |RUS| because full outer join will behave as union.

max(|R|,|S|)<= |RUS| <= |R| + |S|

If in R and S all attributes are not common then minimum number of tuples is |R| + |S|.