Liam Laverty Liam Laverty - 28 days ago 7
MySQL Question

Types of Joins between 2 tables example - Natural Join

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!
enter image description here

Answer

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 INNER or 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