_TABLE_SUFFIX on multiple joins

Question

In BigQuery, standard SQL, how to use _TABLE_SUFFIX on several tables ? See example:

select *
from `table1.*` t1
left join `table2.*` t2 on t1.lel=t2.lel
where _TABLE_SUFFIX between '2017-01-01' and '2017-01-02'    <--- this can't be used 

Am I obliged to create a subquery of table2 with a table_suffix apply to it first ?

Answer 1

In your query _TABLE_SUFFIX is ambiguous, since BigQuery cannot tell whether it comes from t1 or t2. You can disambiguate it with explicit prefix t1. or t2., i.e.

select *
from `table1.*` t1
left join `table2.*` t2 on t1.lel=t2.lel
where t1._TABLE_SUFFIX between '2017-01-01' and '2017-01-02'

Answer 2

also you can add another table_suffix condition for example ,

select *
from `table1.*` t1
left join `table2.*` t2 on t1.lel=t2.lel
where t1._TABLE_SUFFIX between '2017-01-01' and '2017-01-02'
and t2._TABLE_SUFFIX between '2017-01-01' and '2017-01-02'

the different between Mosha answer is that his query will scan all the tables in the left join (higher cost and lower performer) will in the example i sent it will scan only the tables that answer to the conditions in the table suffix,

the only issue with that approach is that bigquery run it like its inner join and not left , for example if you will add condition and t2.tel is null you will received no 0 results