title: Generate a join similar to a vlookup based on closest date
body: <p>I have the following two tables:</p>
  <p><code>movie_sales</code> (provided daily)</p>
  <ul>
  <li>movie_id</li>
  <li>date</li>
  <li>revenue</li>
  </ul>
  <p><code>movie_rank</code> (provided every few days or weeks)</p>
  <ul>
  <li>movie_id</li>
  <li>date</li>
  <li>rank</li>
  </ul>
  <p>The tricky thing is that every day I have data for sales, but only data for ranks once every few days. Here is an example of sample data:</p>
  <pre><code>`movie_sales`
  - titanic (ID), 2014-06-01 (date), 4.99 (revenue)
  - titanic (ID), 2014-06-02 (date), 5.99 (revenue)
  
  `movie_rank`
  - titanic (ID), 2014-05-14 (date), 905 (rank)
  - titanic (ID), 2014-07-01 (date), 927 (rank)
  </code></pre>
  <p>And, because the <code>movie_rate.date</code> of <code>2014-05-14</code> is closer to the two sales dates, the output should be:</p>
  <pre><code>id         date             revenue           closest_rank
  titanic    2014-06-01       4.99               905
  titanic    2014-06-02       5.99               905
  </code></pre>
  <p>The following query works to get the results by getting the min date difference in the sub-select:</p>
  <pre><code>SELECT
      id,
      date,
      revenue,
      (SELECT rank from movie_rank where id=s.id ORDER BY ABS(DATEDIFF(date, s.date)) ASC LIMIT 1)
  FROM
      movie_sales s
  </code></pre>
  <p>But I'm afraid that this would have <strong>terrible</strong> performance as it will literally be doing millions of subselects...on millions of rows. What would be a better way to do this, or is there really no proper way to do this since an index can not be properly done with a <code>DATEDIFF</code> ?</p>