I have attached the question I am struggling with. So far for the question, I have worked out number of tuples for each type of join
I got : R4 = 15(full join, so 10 in first table & 5 in the second)
R3=?, R2 = 5, and R1 = 10.
The correct answer is the fourth bullet point but I am unsure how to obtain this. Any help would be appreciated!
Just consider each join separately, and you can obtain a minumum and maximum row count returned by each.
R1. TBL1 LEFT NATURAL OUTER JOIN TBL2
So TBL1 has 10 rows, therefore 10 rows will be returned, there are no other possibilities regardless of what is in TBL2
R2. TBL1 RIGHT NATURAL OUTER JOIN TBL2
Similar to above, TBL2 has 5 rows, therefore 5 rows are returned. No other possibilities.
R3. TBL1 NATURAL JOIN TBL2
In the absence of an explicit
OUTER on the JOIn MySQL (and all other DBMS as far as I know) will interpret an
INNER JOIN, therefore the highest possible number of rows returned is 5, since there are only 5 rows in TBL2, it is possible to return 0 rows though, if there are no matches.
R4. TBL1 NATURAL FULL OUTER JOIN TBL2
If there are no common values between the two this will return 15 rows, or if all the values in TBL2 are in TBL1, only 10 rows will be returned.
So you have
R1. 10 R2. 5 R3. 0-5 R4 10-15
So rearanging in terms of order of descending number of rows you get:
R4 10-15 R1. 10 R2. 5 R3. 0-5
Which is your answer
R4 >= R1 > R2 >= R3