title: conditional join in bigquery
body: <p>I have two tables.</p>
  
  <p>Table 1 is a single COLUMN of integers.</p>
  
  <p>Table 2 has three COLUMNS : start_integer, end_integer, data</p>
  
  <p>The simple query is to join the column of integers with data where</p>
  
  <pre><code>  integer &gt;= start_integer AND integer &lt;= end_integer
  </code></pre>
  
  <p>In many SQL implementations this can be accomplished with a left conditional JOIN ... ON BETWEEN</p>
  
  <pre><code>SELECT tbl1.integer, tbl2.data FROM tbl1
  LEFT JOIN tbl2 ON tbl1.integer BETWEEN tbl2.start_integer AND 
  tbl2.end_integer;
  </code></pre>
  
  <p>But it seems BigQuery supports only JOIN ON with only an = condition.</p>
  
  <p>This could be accomplished with a cross join, but BigQuery complains that my tables are too big. CROSS JOIN EACH is invalid.</p>
  
  <p>How can I accomplish this join task within the limitations of BigQuery's SQL?</p>
  
  <p>Below is my BigQuery SQL:</p>
  
  <pre><code>SELECT tbl1.integer, tbl2.data
  FROM bq:data.tbl1 
  CROSS JOIN bq:data.tbl2
  WHERE tbl1.integer BETWEEN tbl2.start_integer AND tbl2.end_integer;
  </code></pre>
  
  <p>Which returns the error:</p>
  
  <blockquote>
    <p>Error: 4.1 - 4.132: The JOIN operator's right-side table must be a small table. Switch the tables if the left-side table is smaller, or use JOIN EACH if both tables are larger than the maximum described at <a href="http://goo.gl/wXqgHs" rel="nofollow">http://goo.gl/wXqgHs</a>.</p>
  </blockquote>