Natural Join Operation

1. Another way to reduce the size of temporary results is to choose an optimal ordering of the join operations.
2. Natural join is associative:

3. Although these expressions are equivalent, the costs of computing them may differ.
   • Look again at our expression

   • we see that we can compute deposit branch first and then join with the first part.
   • However, deposit branch is likely to be a large relation as it contains one tuple for every account.
   • The other part,

     is probably a small relation (comparatively).

   • So, if we compute

     first, we get a reasonably small relation.

   • It has one tuple for each account held by a resident of Port Chester.
   • This temporary relation is much smaller than deposit branch.
4. Natural join is commutative:
   • Thus we could rewrite our relational algebra expression as:

   • But there are no common attributes between customer and branch, so this is a Cartesian product.
   • Lots of tuples!
   • If a user entered this expression, we would want to use the associativity and commutativity of natural join to transform this into the more efficient expression we have derived earlier (join with deposit first, then with branch).