title: Joining tables before or after union all?
body: <p>I have 3 tables. 2 are similar.</p>
  
  <pre><code>table1/table2
  col1 string
  col2 string
  col3 integer
  
  table3
  51 columns. strings, ints, doubles, dates
  </code></pre>
  
  <p>I am curious which will be faster.</p>
  
  <pre><code>with s1 as(
    Select *
    from table1
    union all
    Select *
    from table2
  )
  select *
  from s1
  inner join table3 t3
  on s1.col1 = t3.col4
  </code></pre>
  
  <p>or</p>
  
  <pre><code>with s1 as(
    Select *
    from table1 t1
    inner join table3
    on t1.col1 = t3.col4
  ),s2 as(
    Select *
    from table2 t2
    inner join table3
    on t2.col1 = t3.col4
  )
  Select *
  from s1
  union all
  Select *
  from s2
  </code></pre>
  
  <p>Tables are not partioned or indexed. I would like to know how this works for both<br>
  hive and oracle. </p>
  
  <p>Edit 02.02.2017
  I tried to check it in hive. Started at aproximately the same time.</p>
  
  <pre><code>union before join
  Time taken: 539.593 seconds
  
  jbu
  Time taken: 603.071 seconds
  </code></pre>
  
  <p>Unfortunately after I decided to check the result a couple hours later</p>
  
  <pre><code>jbu
  Time taken: 308.205 seconds
  </code></pre>
  
  <p>The result varied depending on how busy the cluster was(( </p>