title: How do I create a list of dates, before doing a LEFT JOIN?
body: <p>I am using BigQuery SQL. I have the following tables:</p>
  
  <pre><code>Table "public.org" (records all organisations)
      Column    │          Type          │ Modifiers
  ──────────────┼────────────────────────┼───────────
   code         │ character varying(6)   │ not null
   name         │ character varying(200) │ not null   
   setting      │ integer                │ not null    
  
   Table "public.spending" (records spending on chemical by org by month)
      Column         │          Type           │ Modifiers
  ───────────────────┼─────────────────────────┼───────────
   org_id            │ character varying(6)    │ not null
   month             │ date                    │ not null
   chemical_id       │ character varying(9)    │ not null
   actual_cost       │ double precision        │ not null
  </code></pre>
  
  <p>And I want to calculate the spending on a particular chemical by month, by organisation. The complication is if there was no spending by an organisation on that chemical in a month, there is simply no entry in the <code>spending</code> table, rather than a zero entry. However, I would like output (a null or zero result, I don't mind which).</p>
  
  <p>Right now I have this, which gives me total spending for all organisations including those that had no entries, but does not separate spending out by month:</p>
  
  <pre><code>SELECT
    org.code AS code,
    org.name AS name,
    num.actual_cost as actual_cost
  FROM (
    SELECT
      code,
      name
    FROM
      org
    WHERE
      setting=4) AS orgs
  LEFT OUTER JOIN EACH (
    SELECT
      org_id,
      SUM(actual_cost) AS actual_cost
    FROM
      spending
    WHERE
      chemical_id='1202010U0AAAAAA'
    GROUP BY
      org_id) AS num
  ON
    num.org_id = orgs.code
  </code></pre>
  
  <p>So now I need to extend it to do a LEFT JOIN by month <em>and</em> organisation. I know that I can get the unique months in the <code>spending</code> table by doing this:</p>
  
  <pre><code>SELECT month FROM spending GROUP BY month
  </code></pre>
  
  <p>(NB BigQuery doesn't support <code>UNIQUE</code>.)</p>
  
  <p>But how do I get all the unique rows for month <em>and</em> organisation, and only <em>then</em> do a LEFT JOIN onto the spending?</p>