title: Hive - Optimising a self-join
body: <p>Let's say I have the following query:</p>
  
  <pre><code>select a.model, a.engine_size, b.engine_size from (
  
    select model, engine_size
    from cars
    where number_of_doors = 4
  ) a
  
  inner join (
  
    select model, engine_size
    from cars
    where number_of_doors = 4
  ) b
  
  on (a.model = b.model);
  </code></pre>
  
  <p>I'm repeating a subquery here. I'm just wondering if the following is more 'optimal' or will the repeated subquery's result automatically be cached?</p>
  
  <pre><code>with features as (
  
    select model, engine_size
    from cars
    where number_of_doors = 4
  )
  
  select a.model, a.engine_size, b.engine_size
  from features a
  inner join features b
  on (a.model = b.model);
  </code></pre>
  
  <p>Is either of these going to be more efficient?</p>