title: Left outer join in Hive causing reduction in rows
body: <p>I'm using Hive to join tables using <code>left outer join</code> with the expectation that, since it is an outer join, the number of row in the left column should not reduce. However, that is not what I'm seeing.</p>
  
  <p>Say table A has 100k rows and table B has 200k. My assumption is this, if I do a left outer join as such <code>select ... from A left outer join B on A.x=B.x</code>, I should never see rows fewer than 100k (A's size). In my case, I AM seeing a reduction in rows! I'm trying to see how I can replicate in a way that I can share on SO. In the mean time, if my assumption is wrong, please let me know.</p>
  
  <p>There is one potential wrinkle in my query. There is an additional where clause as such:</p>
  
  <pre><code>SELECT ... 
  FROM A 
  LEFT outer join B on A.x=B.x 
  WHERE (B.y is null or B.y is between date_sub(A.y,5) and date_sub(A.y,1)
  </code></pre>
  
  <p>Notice that I'm being careful about checking B.y for null value so it keeps left outer join semantics.</p>
  
  <p>EDIT:
  A couple of people have mentioned moving the where clause to the join. However, this causes problems. First it complains that 'or' isn't supported in join clause. If I remove that (since null check should no longer be required), I get this error:</p>
  
  <p><code>Both left and right aliases encountered in JOIN</code></p>
  
  <p>This is probably because the <code>where</code> clause references table A as well as table B (it isn't static)</p>
  
  <p>Any ideas?</p>