title: When to use R, when to use SQL?
body: <p>I have a moderate sized database with many joins and lookup tables.</p>
  
  <p>I am more familiar with R than with SQL, and I am using MySQL. </p>
  
  <h2>My Question:</h2>
  
  <p>At what point is it beneficial to stop increasing the complexity of an SQL statement in favor of the data subsetting functionality in R (e.g., <code>merge</code>, <code>*apply</code>, <code>maply</code>, <code>dlply</code>, etc.)in R.</p>
  
  <p>On one hand, SQL's join is easier than selecting all contents of each table and using the R <code>merge</code> function to join them. Also, doing the conditional selects in SQL would reduce the amount of data that has to be imported to R; but the speed difference is not significant. </p>
  
  <p>On the other hand, a big join with a complex where clause becomes less easy to understand than the R syntax.</p>
  
  <p>Below I have some <em>untested</em> code for illustrative purposes: I am asking this question at before having working code, and the answer to my question doesn't require working code (although this is always appreciated) - the "most elegant approach", "fewest lines", or "amazing implementation of X" are always appreciated, but what I am particularly interested in is the "most sensible / practical / canonical / based on first principles" rationale. </p>
  
  <p>I am interested in the general answer of which steps should use a SQL <code>where</code> clause and which steps would be easier to accomplish using R.</p>
  
  <h2>Illustration:</h2>
  
  <h3>Database description</h3>
  
  <p>there are three tables: <code>a</code>, <code>ab</code>, and <code>b</code>. Tables <code>a</code> and <code>b</code> each have a primary key <code>id</code>. They have a many-many relationship that is represented by a lookup table, <code>ab</code>, which contains fields <code>ab.a_id</code> and <code>ab.b_id</code> that join to <code>a.id</code> and <code>b.id</code>, respectively. Both tables have a <code>time</code> field, and a has a <code>group</code> field.</p>
  
  <h3>Goal:</h3>
  
  <p>Here is a minimal example of the join and subsetting that I want to do;</p>
  
  <p>(MySQL naming of elements, e.g. <code>a.id</code> is equivalent to <code>a$id</code> in R)</p>
  
  <ol>
  <li><p>Join tables <code>a</code> and <code>b</code> using <code>ab</code>, appending multiple values of <code>b.time</code> associated with each <code>a.id</code> as a new column;</p>
  
  <pre><code>select a_time, b.time, a.id, b.id from 
         a join ab on a.id = ab.a_id 
         join b on b.id = ab.b_id and then append b.time for distinct values of b.id;
  </code></pre></li>
  <li><p>I don't need repeated values of b.time, I only need a value of <code>b.max</code>: for repeated values of <code>b.time</code> joined to each <code>a.id</code>, <code>b.max</code> is the value of <code>b.time</code> closest to but not greater than <code>a.time</code></p>
  
  <pre><code>b.max &lt;- max(b.time[b.time &lt; a.time))
  </code></pre></li>
  <li>append the value <code>dt &lt;- a.time - b.max</code> to the table, for example, in R, </li>
  <li><p>for each distinct value in <code>a.group</code>,   select which(min(x.dt)))</p>
  
  <pre><code>x.dt &lt;- a.time - b.max
  </code></pre></li>
  </ol>