sql - conditional join in bigquery -

i have 2 tables.

table 1 single column of integers.

table 2 has 3 columns : start_integer, end_integer, data

the simple query join column of integers data where

  integer >= start_integer , integer <= end_integer 

in many sql implementations can accomplished left conditional join ... on between

select tbl1.integer, tbl2.data tbl1 left join tbl2 on tbl1.integer between tbl2.start_integer ,  tbl2.end_integer; 

but seems bigquery supports join on = condition.

this accomplished cross join, bigquery complains tables big. cross join each invalid.

how can accomplish join task within limitations of bigquery's sql?

below bigquery sql:

select tbl1.integer, tbl2.data bq:data.tbl1  cross join bq:data.tbl2 tbl1.integer between tbl2.start_integer , tbl2.end_integer; 

which returns error:

error: 4.1 - 4.132: join operator's right-side table must small table. switch tables if left-side table smaller, or use join each if both tables larger maximum described @ http://goo.gl/wxqghs.

good news (2016)! bigquery support inequality joins - make sure uncheck "use legacy sql option".

example query:

select *  (   select 1 x ) join (   select 2 y ) b on a.x<b.y 

with legacy sql:

error: on clause must , of = comparisons of 1 field name each table, ... 

with standard sql:

1     2 

• docs: https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql
• discussion: https://code.google.com/p/google-bigquery/issues/detail?id=448#c31