title: Efficiently grouping elements that exists in both documents (inner join) in Xquery
body: <p>I have the following data:</p>
  
  <pre><code>&lt;Subjects&gt;
      &lt;Subject&gt;
          &lt;Id&gt;1&lt;/Id&gt;
          &lt;Name&gt;Maths&lt;/Name&gt;
      &lt;/Subject&gt;
      &lt;Subject&gt;
          &lt;Id&gt;2&lt;/Id&gt;
          &lt;Name&gt;Science&lt;/Name&gt;
      &lt;/Subject&gt;
      &lt;Subject&gt;
          &lt;Id&gt;2&lt;/Id&gt;
          &lt;Name&gt;Advanced Science&lt;/Name&gt;
      &lt;/Subject&gt;
  &lt;/Subjects&gt;
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>&lt;Courses&gt;
      &lt;Course&gt;
          &lt;SubjectId&gt;1&lt;/SubjectId&gt;
          &lt;Name&gt;Algebra I&lt;/Name&gt;
      &lt;/Course&gt;
      &lt;Course&gt;
          &lt;SubjectId&gt;1&lt;/SubjectId&gt;
          &lt;Name&gt;Algebra II&lt;/Name&gt;
      &lt;/Course&gt;
      &lt;Course&gt;
          &lt;SubjectId&gt;1&lt;/SubjectId&gt;
          &lt;Name&gt;Percentages&lt;/Name&gt;
      &lt;/Course&gt;
      &lt;Course&gt;
          &lt;SubjectId&gt;2&lt;/SubjectId&gt;
          &lt;Name&gt;Physics&lt;/Name&gt;
      &lt;/Course&gt;
      &lt;Course&gt;
          &lt;SubjectId&gt;2&lt;/SubjectId&gt;
          &lt;Name&gt;Biology&lt;/Name&gt;
      &lt;/Course&gt;
  &lt;/Courses&gt;
  </code></pre>
  
  <p>I wish to efficiently get elements from both documents that share the share the same Ids. </p>
  
  <p>I want to get the result like this:</p>
  
  <pre><code>&lt;Results&gt;
      &lt;Result&gt;
          &lt;Table1&gt;
              &lt;Subject&gt;
                  &lt;Id&gt;1&lt;/Id&gt;
                  &lt;Name&gt;Maths&lt;/Name&gt;
              &lt;/Subject&gt;
          &lt;/Table1&gt;
          &lt;Table2&gt;
              &lt;Course&gt;
                  &lt;SubjectId&gt;1&lt;/SubjectId&gt;
                  &lt;Name&gt;Algebra I&lt;/Name&gt;
              &lt;/Course&gt;
              &lt;Course&gt;
                  &lt;SubjectId&gt;1&lt;/SubjectId&gt;
                  &lt;Name&gt;Algebra II&lt;/Name&gt;
              &lt;/Course&gt;
              &lt;Course&gt;
                  &lt;SubjectId&gt;1&lt;/SubjectId&gt;
                  &lt;Name&gt;Percentages&lt;/Name&gt;
              &lt;/Course&gt;
          &lt;/Table2&gt;
      &lt;/Result&gt;
      &lt;Result&gt;
          &lt;Table1&gt;
              &lt;Subject&gt;
                  &lt;Id&gt;2&lt;/Id&gt;
                  &lt;Name&gt;Science&lt;/Name&gt;
              &lt;/Subject&gt;
              &lt;Subject&gt;
                  &lt;Id&gt;2&lt;/Id&gt;
                  &lt;Name&gt;Advanced Science&lt;/Name&gt;
              &lt;/Subject&gt;
          &lt;/Table1&gt;
          &lt;Table2&gt;
              &lt;Course&gt;
                  &lt;SubjectId&gt;2&lt;/SubjectId&gt;
                  &lt;Name&gt;Physics&lt;/Name&gt;
              &lt;/Course&gt;
              &lt;Course&gt;
                  &lt;SubjectId&gt;2&lt;/SubjectId&gt;
                  &lt;Name&gt;Biology&lt;/Name&gt;
              &lt;/Course&gt;
          &lt;/Table2&gt;
      &lt;/Result&gt;
  &lt;/Results&gt;
  </code></pre>
  
  <p>So far I have 2 solutions:</p>
  
  <pre><code>&lt;Results&gt;       
  {
     for $e2 in $t2/Course
     let $foriegnId := $e2/SubjectId
     group by $foriegnId
     let $e1 := $t1/Subject[Id = $foriegnId]
     where $e1
     return
        &lt;Result&gt;
           &lt;Table1&gt;
              {$e1}
           &lt;/Table1&gt;
           &lt;Table2&gt;
              {$e2}
           &lt;/Table2&gt;
        &lt;/Result&gt;
  }
  &lt;/Results&gt;
  </code></pre>
  
  <p>and the otherway round:</p>
  
  <pre><code>&lt;Results&gt;       
  {
     for $e1 in $t1/Subject
     let $id := $e1/Id
     group by $id
     let $e2 := $t2/Course[SubjectId = $id]
     where $e2
     return
        &lt;Result&gt;
           &lt;Table1&gt;
              {$e1}
           &lt;/Table1&gt;
           &lt;Table2&gt;
              {$e2}
           &lt;/Table2&gt;
        &lt;/Result&gt;
  }
  &lt;/Results&gt;
  </code></pre>
  
  <p>Is there a more efficient way of doing this?
  Perhaps taking advantages of multiple groups?</p>
  
  <p><strong>Update</strong>
  A major issue with my code at the moment is that it's performance is highly dependent on which table is bigger. For example the 1st solution is better in cases where the 2nd table is bigger and vice versa. </p>