Liam Laverty - 3 months ago 18

MySQL Question

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!

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