title: BigQuery Join Array in standard SQL
body: <p>I am using standard SQL and I have table <code>Order</code>:</p>
  
  <p><a href="http://i.stack.imgur.com/szFAh.png" rel="noreferrer">"Order" table</a></p>
  
  <p>and I am trying to join it with table <code>MenuItem</code></p>
  
  <p><a href="http://i.stack.imgur.com/sMwbZ.png" rel="noreferrer">"MenuItem" table</a></p>
  
  <p>on <code>Order</code> item_ids array and <code>MenuItem</code> __id__ integer column and get array of <code>MenuItem</code> prices, but I am getting an error:</p>
  
  <blockquote>
    <p>Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.</p>
  </blockquote>
  
  <p>How to avoid this error?</p>
  
  <p>Query:</p>
  
  <pre><code>WITH menu_items AS 
  ( 
      SELECT
          __id__,
          price
      FROM
          `potykion.MenuItem`
  )
  SELECT
      *, 
      ARRAY(
          SELECT 
             price
          FROM 
             UNNEST(item_ids) AS id
          JOIN 
              menu_items 
          ON 
              id = menu_items.__id__
      ) 
  FROM 
      `potykion.Order`
  </code></pre>