title: Oracle: Get data on all months, 0 if no data
body: <p>I have an Mview that brings data group by <code>idNumber</code> and <code>Month</code>. So I want to display 0 if there is no data for an specific month. This is my query:</p>
  
  <pre><code>select MonthName, myCost, myNumber 
    from
       (
         select MONTH mm, myCost, myNumber
           from myOracle_mv
       ) myTotals,
       (
         select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
          from dual
        connect by level &lt;= 12
       ) ALLMONTHS
  where mm = MonthName
  </code></pre>
  
  <p>So I was expecting:</p>
  
  <pre><code>Month Number Data
  -----------------------
  1     abc123 4444
  2     0
  3     abc123 4444
  4     abc123 4444
  5     0
  6     abc123 4444
  7     abc123 4444
  8     0
  9     abc123 4444
  10    abc123 4444
  11    0
  12    abc123 4444
  </code></pre>
  
  <p>Instead I'm still getting:</p>
  
  <pre><code>1    abc123  4444
  3    abc123  4444
  4    abc123  4444
  6    abc123  4444
  7    abc123  4444
  9    abc123  4444
  10   abc123  4444
  12   abc123  4444
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p>Thanks!</p>
  
  <p><b><i>EDIT: Thanks for the answers. I did have the outer join in my Query but forgot to type in because was concentrating in changes the names of the table/columns.</i></b></p>
  
  <p>So yes, I have tried with the OUTER JOIN and I still not getting the expected results. Any feedback is greatly appreciated.</p>
  
  <p>EDIT:  This is the data on myOracle_MV:</p>
  
  <pre><code>3777.24     AAA 1   2012
  49973.12    AAA 2   2012
  4049.91     AAA 3   2012
  469.485     AAA 4   2012
  5872.22     AAA 5   2012
  65837.71    AAA 6   2012
  566.23      AAA 7   2012
  18432.95    AAA 8   2012
  4337.75     AAA 12  2011
  18811       BBB 1   2012
  29872.67    BBB 2   2012
  29068.55    BBB 3   2012
  264957.8    BBB 4   2012
  67673       BBB 5   2012
  855.02      BBB 6   2012
  5226.1      BBB 7   2012
  2663.24     BBB 8   2012
  5490.58     BBB 12  2011
  3845.47     CCC 1   2012
  3050.54     CCC 2   2012
  3784.44     CCC 3   2012
  799.73      CCC 4   2012
  124884.2    CCC 5   2012
  5157.24     CCC 6   2012
  19184.78    CCC 7   2012
  2280.05     CCC 8   2012
  107.07      DDD 3   2012
  181.78      DDD 4   2012
  110.09      DDD 5   2012
  18016.19    DDD 6   2012
  1772.95     DDD 7   2012
  63.32       DDD 8   2012
  </code></pre>