Duplicate column names in BigQuery when selecting twice within the same table

I am trying to perform the following query in BigQuery:

SELECT * FROM
  (
    SELECT
      date,
      grob as `grob_r1`
    FROM `mytab_tbl`
    WHERE region = "r1"
  )
  AS TAB_1,
  (
    SELECT
      date,
      grob as `grob_r2`
    FROM `mytab_tbl`
    WHERE region = "r2"
  )
  AS TAB_2
WHERE TAB_1.date = TAB_2.date

It is basically a selection on the same table applying different condition rules, with the aim to then create a view. However, I get the following error: Duplicate column names in the result are not supported. Found duplicate(s): date

Do you have any idea about how to fix this?

Answer

Mainly, your SELECT * will just get all columns from TAB_1 and TAB_2, and in both cases, you have the date field.

SELECT TAB_1.*, TAB_2.grob_r2 FROM
  (
    SELECT
      date,
      grob as `grob_r1`
    FROM `mytab_tbl`
    WHERE region = "r1"
  )
  AS TAB_1,
  (
    SELECT
      date,
      grob as `grob_r2`
    FROM `mytab_tbl`
    WHERE region = "r2"
  )
  AS TAB_2
WHERE TAB_1.date = TAB_2.date

In your case, your join condition is an equality, but if you wanted to get the date from TAB_2 as well in your results, or if you want to get all columns from TAB_2, whatever they are, you could do something like

SELECT TAB_1.*, TAB_2.* EXCEPT(date), TAB_2.date AS tab_2_date FROM ...

edit: To help you with the FULL JOIN question.

SELECT TAB_1.*, TAB_2.grob_r2 FROM
  (
    SELECT
      date,
      grob as `grob_r1`
    FROM `mytab_tbl`
    WHERE region = "r1"
  )
  AS TAB_1 FULL JOIN 
  (
    SELECT
      date,
      grob as `grob_r2`
    FROM `mytab_tbl`
    WHERE region = "r2"
  )
  AS TAB_2
ON TAB_1.date = TAB_2.date