title: BigQuery: IN a temporary table
body: <p>I have a long SQL query looking like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT AVG(total_sum) AS avg_total_sum, COUNT(*) AS cnt
  FROM (
    SELECT order_id, ...
    FROM `project.dataset.orders`
    WHERE order_id NOT IN (
      SELECT order_id
      FROM `project.dataset.orders`
      CROSS JOIN UNNEST(gifts) AS gifts
      WHERE ...
    )
  )
  </code></pre>
  
  <p>And this works well, but I'd like to simplify the code by splitting it in temporary tables, like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH t_ids AS (
    SELECT order_id
    FROM `project.dataset.orders`
    CROSS JOIN UNNEST(gift_details) AS gift_details
    WHERE ...
  )
  
  SELECT AVG(total_sum) AS avg_total_sum, COUNT(*) AS cnt
  FROM (
    SELECT order_id, ...
    FROM `project.dataset.orders`
    WHERE order_id NOT IN t_ids
  )
  </code></pre>
  
  <p>But it leads to the following error:</p>
  
  <blockquote>
    <p>Syntax error: Expected "(" or keyword UNNEST but got identifier "t_ids"</p>
  </blockquote>
  
  <p>I tried to add parentheses, <code>UNNEST</code>, but it seems to be not working with temp table. How the problem can be solved?</p>