title: BigQuery: Union two different tables which are based on federated Google Spreadsheet
body: <p>I have two different Google Spreadsheet:</p>
  
  <p>One with 4 columns</p>
  
  <pre><code>+------+------+------+------+
  | Col1 | Col2 | Col5 | Col6 |
  +------+------+------+------+
  | ID1  | A    | B    | C    |
  | ID2  | D    | E    | F    |
  +------+------+------+------+
  </code></pre>
  
  <p>One with the 4 columns of the previous file, and 2 more columns</p>
  
  <pre><code>+------+------+------+------+------+------+
  | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
  +------+------+------+------+------+------+
  | ID3  | G    | H    | J    | K    | L    |
  | ID4  | M    | N    | O    | P    | Q    |
  +------+------+------+------+------+------+
  </code></pre>
  
  <p>I configured them as Federated source in Google BigQuery, now I need to create a view that will join data of both tables.</p>
  
  <p>Both tables have <code>Col1</code> column, which contains an ID, this ID is unique across alla the tables, does not contain replicated data.</p>
  
  <p>The resulting table I'm looking for is the following one:</p>
  
  <pre><code>+------+------+------+------+------+------+
  | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |
  +------+------+------+------+------+------+
  | ID1  | A    | NULL | NULL | B    | C    |
  | ID2  | D    | NULL | NULL | E    | F    |
  | ID3  | G    | H    | J    | K    | L    |
  | ID4  | M    | N    | O    | P    | Q    |
  +------+------+------+------+------+------+
  </code></pre>
  
  <p>For the columns that the first file does not have, I'm expecting a <code>NULL</code> value.</p>
  
  <p>I'm using standardSQL, here is a statement you can use to generate a sample data:</p>
  
  <pre><code>#standardsQL
  
  WITH table1 AS (
    SELECT "A" as Col1, "B" as Col2, "C" AS Col3
    UNION ALL
    SELECT "D" as Col1, "E" as Col2, "F" AS Col3
  ),
  
  table2 AS (
    SELECT "G" as Col1, "H" as Col2, "J" AS Col3, "K" AS Col4, "L" AS Col5
    UNION ALL
    SELECT "M" as Col1, "N" as Col2, "O" AS Col3, "P" AS Col4, "Q" AS Col5
  )
  </code></pre>
  
  <p>A simple <code>UNION ALL</code> is not working because tables have different columns</p>
  
  <pre><code>SELECT * FROM table1
  UNION ALL
  SELECT * FROM table2
  
  Error: Queries in UNION ALL have mismatched column count; query 1 has 3 columns, query 2 has 5 columns at [17:1]
  </code></pre>
  
  <p>And wildcard operator is not a suitable way because Federated sources does not support that</p>
  
  <pre><code>SELECT * FROM `table*`
  
  Error: External tables cannot be queried through prefix
  </code></pre>
  
  <p>Of course this is a sample data, with only 3-5 columns, the real tables have 20-40 columns. So an example where I need to explicitly <code>SELECT</code> field by field it is not a considerable way.</p>
  
  <p>Is there a working way to join this two tables?</p>