Given relations A(a,b,c), B(e,f), C(d,g,h), where A has 800 tuples, B 200 and c 500. In worst case gives the expression A * B * C ( with * natural join) :
a) 800 tuples b) 200 tuples c) 500 tuples d) 800*200*500 tuples e) 800+200+500 tuples f) Nothing from the above.
My guess was 800+200+500 since there isnt any common attribute ? And what if there was a common attribute ?
Advertisement
Answer
A natural join on tables that have no rows in common is in fact a cross join as you so rightly suppose. You’ll get A * B * C = 800 * 200 * 500 = 80,000,000 rows.
Once the tables have columns in common a filter takes place. Depending on whether there are matches and how many, you get anything from 0 to 80,000,000 rows. Examples:
- If all tables have one column in common and its value is the same in every row in every table, you end up with all combinations again.
- If all tables have one column in common and its value is ‘A’ in all rows in table A, ‘B’ in all rows in table ‘B’ and ‘C’ in all rows in table C, you end up with no matches, i.e. zero rows.
After all, this all is dull theory, because nobody in their right mind would ever use a natural join 🙂