title: MySQL - select missing dates from table?
body: <p>Suppose you have the following table <code>values</code>:</p>
  
  <pre><code>date       | value
  2012-01-01 |    8
  2012-01-02 |    3
  2012-01-03 |   17
  2012-01-09 |  100
  2012-01-12 |    2
  </code></pre>
  
  <p>Now suppose you want to select all the dates between 2012-01-02 and 2012-01-12 and show their values if present. If you simply query the table for the appropriate date range, the dates that don't have values are going to be absent, for obvious reasons. Is there a way to fill in those dates in the query?</p>
  
  <p>The obvious solution is to create a table <code>dates</code> that just stores a list of all dates that may come up, and then to select from the <code>dates</code> table and join <code>values</code> to it, but I'd like to have a solution that doesn't rely on creating a single-column table if I can.</p>
  
  <p>Of note: there are <a href="https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range">existing</a> <a href="https://stackoverflow.com/questions/2844486/mysql-to-fill-in-missing-dates-when-using-group-by-datetable-timestamp-without">questions</a> on <a href="https://stackoverflow.com/questions/2501993/mysql-filling-in-missing-dates">SO</a> on this topic, but they are all from 2010 (at least the ones I found when searching were), and MySQL features have grown in that time; there may be a dynamic solution now. If that's not the case, and the <code>dates</code> table is still the best solution, then this question should be closed as a duplicate.</p>