title: BigQuery: Union / Join two tables with many columns, some overlapping
body: <p>I have two large tables with some overlapping columns, some of which contain the same values in the overlapping columns. Here's a toy example (in the actual example, there are dozens of columns, both those that overlap and those that don't):</p>
  
  <pre><code>Table 1: a, b, c
  
  Table 2: a, d, e
  </code></pre>
  
  <p>Some values of <code>a</code> are in only one table, some are in both.</p>
  
  <p>Is there a query that will let me generate a table with all values where available:</p>
  
  <pre><code>Table 3: a, b, c, d, e
  </code></pre>
  
  <p>My current query requires listing every column, which is very verbose with dozens of columns, and inflexible when the schema changes:</p>
  
  <pre><code>SELECT
    coalesce(t1.a,
      t2.a) AS a,
    t1.b,
    t1.c,
    t2.d,
    t2.e
  FROM
    t1
  FULL JOIN
    t2
  USING
    (a)
  </code></pre>
  
  <p>Things I've tried: <code>UNION</code> seems to require the same schema, <code>SELECT t1.*, t2.*</code> raises an error on overlapping columns, <code>SELECT t1.* ... USING (a)</code> will give nulls for values in <code>a</code> where there are values only in <code>t1.a</code>.</p>