title: Natural join of table (A) with itself
body: <p>I came across this question in an interview and I have been wondering if what I did was right. Let's say I have a table 'A' with the following attributes:</p>
  
  <pre><code>R   S    T
  -----------
  a1  b1   c1
  a1  b2   c2
  a1  b3   c3
  a4  b4   c4
  </code></pre>
  
  <p>and lets say I need to calculate the Relational Algebra for given B = {[(projection)R,S (A) NATURAL JOIN (projection) S,T (A) ] NATURAL JOIN (projection)R,T (A)}</p>
  
  <p>what would be the result?</p>
  
  <h2>This is what I tried:</h2>
  
  <pre><code>-We know (A) NATURAL JOIN (A) = A
  -I did the first set of join within the square bracket. Since we had  attribute 'S' in common I just yielded the result to be a table of (R S T) with the same 4 rows of tuples.
  -Finally, I joined (R S T) with the second set of join where attributes 'R' and 'T' are common which I assumed will yield R S T again with 4 rows of tuples.
  </code></pre>
  
  <p>Meaning, with the way I did it, I ended up getting B = A.</p>
  
  <p>I did not consider the tuples at all, I just did a natural join based on the common attributes between two projections.</p>
  
  <p>I know that's very stupid.. but I am trying to execute it in MySQL and for some reason I am getting errors when I try to execute such a query:</p>
  
  <p>select A,B from dbt2.relationalalgebra as r1 NATURAL JOIN (select B, C from dbt2.relationalalgebra as r2); and I am getting an error saying every derived table must have its own aliases!</p>
  
  <p>Please help me clarify on how Natural join works on same table.</p>
  
  <p>Thanks in advance for any help.</p>