title: Translating SQL joins on foreign keys to R data.table syntax
body: <p>The <a href="http://cran.r-project.org/web/packages/data.table/index.html"><code>data.table</code></a> package provides many of the same table handling methods as SQL. If a table has a key, that key consists of one or more columns. But a table can't have more than one key, because it can't be sorted in two different ways at the same time.</p>
  
  <p>In this example, <code>X</code> and <code>Y</code> are <code>data.table</code>s with a single key column "id"; <code>Y</code> also has a non-key column "x_id".</p>
  
  <pre><code>   X &lt;- data.table(id = 1:5, a=4:8,key="id")
     Y &lt;- data.table(id = c(1,1, 3,5,7), x_id=c(1,4:1), key="id")
  </code></pre>
  
  <p>The following syntax would join the tables on their keys:</p>
  
  <pre><code>  X[Y]
  </code></pre>
  
  <p>How can I translate the following SQL syntax to data.table code?</p>
  
  <pre><code>  select * from X join Y on X.id = Y.x_id; 
  </code></pre>
  
  <p>The closest that I have gotten is:</p>
  
  <pre><code>Y[X,list(id, x_id),by = x_id,nomatch=0]
  </code></pre>
  
  <p>However, this does not do the same inner join as the SQL statement.</p>
  
  <hr>
  
  <p>Here is a more clear example in which the foreign key is y_id, and we want the join to look up values of Y2 where <code>X2$y_id = Y2$id</code>. </p>
  
  <pre><code>    X2 &lt;- data.table(id = 1:5, y_id = c(1,1,2,2,2), key="id")
      Y2 &lt;- data.table(id = 1:5, b = letters[1:5], key="id")
  </code></pre>
  
  <p>I would like to produce the table:</p>
  
  <pre><code>   id  y_id  b
      1     1 "a"
      2     1 "a"
      3     2 "b"
      4     2 "b"
      5     2 "b"
  </code></pre>
  
  <p>similar to what is done by the following kludge:</p>
  
  <pre><code>&gt; merge(data.frame(X2), data.frame(Y2), by.x = "y_id", by.y = "id")
    y_id id b
  1    1  1 a
  2    1  2 a
  3    2  3 b
  4    2  4 b
  5    2  5 b
  </code></pre>
  
  <p>However, when I do this:</p>
  
  <pre><code>    X2[Y2, 1:2,by = y_id]
  </code></pre>
  
  <p>I do not get the desired result:</p>
  
  <pre><code>    y_id V1
  [1,]    1  1
  [2,]    1  2
  [3,]    2  1
  [4,]    2  2
  </code></pre>