title: Efficiently joining IP ranges in BigQuery
body: <p>I have a table of IP addresses and a table of IP address ranges (start ip, end ip) that I'd like to join together. I've been able to make this work with the following query:</p>
  
  <pre><code>SELECT * FROM `ips` i
  JOIN `ranges`  a
  ON NET.SAFE_IP_FROM_STRING(i.ip) 
  BETWEEN NET.SAFE_IP_FROM_STRING(a.start_ip) 
  AND NET.SAFE_IP_FROM_STRING(a.end_ip)
  </code></pre>
  
  <p>The problem I'm having is that it scales really badly. To do it for 10 IPs takes around 8 seconds, 100 takes 30 seconds, and 1000 takes a few minutes. I'd like to be able to do this for tens of millions of rows. (I have tried writing the output of <code>NET.SAFE_IP_FROM_STRING</code> to the ranges table, but it only speeds things up by around 10%, and doesn't help with scaling).</p>
  
  <p>The ranges don't overlap, so for every row in the input table I expect 0 or 1 rows in the output table. A <code>LATERAL JOIN</code> would let me do that and almost certainly speed things up, but I don't think BigQuery supports them. Is there any other way to make this query faster and scalable?</p>