title: BigQuery - Correlated subquery over union not working
body: <p>When running a simple correlated subquery (below) over a union I get 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>If I remove the union, the query works fine. How can I get the same result without union?</p>
  
  <pre><code>with data as (
  
  
   select * from `bigquery-public-data.samples.shakespeare` 
    union all 
    select * from `bigquery-public-data.samples.shakespeare` 
  
  )
  
  select
     *,
     (select count(*) from data where word = d.word) word_count_2
  from `bigquery-public-data.samples.shakespeare` d
  </code></pre>