title: R data table merge vs Stata merge
body: <p>I am comparing R data.table merge with Stata's merge command.</p>
  
  <p>Say we have two datasets, X and Y. Both have a common key <code>id</code>.</p>
  
  <p>Stata has 1:1 <code>merge</code> which means datasets will be <code>merge</code>d one to one on a unique id. If we are using dataset X in Stata and wish to merge with Y, "master" is X and "using" is Y.</p>
  
  <pre><code>merge 1:1 id using Y, options
  </code></pre>
  
  <p>Stata further gives you a choice of as they called it "matched result". Basically it is a choice of 3 values.</p>
  
  <pre><code>Numeric Code    Description
             1    observation appeared in X only
             2    observation appeared in Y only
             3    observation appeared in both only
  </code></pre>
  
  <p>I believe that in R data.table, a 1:1 merge it is: </p>
  
  <p>For "merged result" numeric code 1, you do LEFT JOIN. Then any result not appearing in X will be given NA. So we need to do </p>
  
  <pre><code>Y[X, on=.(id)] or merge(X,Y, all.x=TRUE, on="id")
  </code></pre>
  
  <p>For "merged result" numeric code 2, you do RIGHT JOIN. Then any result not appearing in Y will be given NA. So we need to do </p>
  
  <pre><code>X[Y, on=.(id)] or merge(X,Y, all.y=TRUE, on="id")
  </code></pre>
  
  <p>For "merged result" numeric code 3, you do INNER JOIN. It only displays data that exist in both X and Y.</p>
  
  <pre><code>X[Y, nomatch=0, on=.(id)] or merge(X,Y, all=FALSE, on="id")
  </code></pre>
  
  <p>Is this correct? I am comfortable with Stata choice 3 which is basically INNER JOIN. But I am confused with choices 1 and 2. If you further do na.omit(), it will revert to choice 3. Is that correct?</p>
  
  <p>And what can I do in R for 1:m or m:1 one to many and vice versa?</p>
  
  <p>FYI for Stata manual:</p>
  
  <pre><code>https://www.stata.com/manuals/dmerge.pdf 
  </code></pre>
  
  <p>For data.table merge:</p>
  
  <pre><code>https://rstudio-pubs-static.s3.amazonaws.com/52230_5ae0d25125b544caab32f75f0360e775.html
  </code></pre>