union all two table instead of join

I have several table which I can not join them as it gets really complicated and bigquery is not able to process it. So I am trying to union all tables and then group by. I have an issue during this process. I have two tables called t1 and t2 with below headers, they don’t have null values:

a.   b.   c.   d.                a.   b.   c.   e.   

so in order to union all and group them I have below code:

WITH
  all_tables_unioned AS (
  SELECT
    *,
    NULL e
  FROM
    `t1`
  UNION ALL
  SELECT
    *,
    NULL d
  FROM
    `t2` )
SELECT
  a,
  b,
  c,
  MAX(d) AS d,
  MAX(e) AS e
FROM
  all_tables_unioned
GROUP BY
  a,
  b,
  c

unfortunately when I run this I get a table a,b,c,d,e which e column is all null! I tried to run query for each table before union all to make sure they are not null. I do not really know what is wrong with my query.

Answer

union all does not go by column names. Just list all the columns explicitly:

WITH all_tables_unioned AS (
      SELECT a, b, c, d, NULL as e
      FROM `t1`
      UNION ALL
      SELECT a, b, c, NULL as d, e
      FROM `t2`
     )

Regardless of the names you assign, the union all uses positions for matching columns.