title: Hive - OR condition with left outer join
body: <p>I have referred all the queries on SO for a similar case. Although the error may be common, I am looking for solution for the specific case. Please refrain from marking the question duplicate, unless you get exactly the same scenario with an accepted solution.</p>
  
  <hr>
  
  <p>I have two tables</p>
  
  <pre><code>Main table:
  
  c1  c2  c3  c4  c5
  1   2   3   4   A
  
  Other table
  
  c1  c2  c3  c4  c5
  1   8   5   6   B
  8   2   8   9   C
  8   7   3   9   C
  8   7   9   4   C
  5   6   7   8   D
  </code></pre>
  
  <p>Now, from the other table, I should only be able to pick only unique record across all the column. e.g. the last row (<code>5,6,7,8, D</code>) only.</p>
  
  <p>Row 1 from other table rejected, because c1 value (1) is same as c1 value (1) in main table, Row 2 rejected because c2 value of other and main table matches and likewise...</p>
  
  <p>In a nutshell, none of the columns from other table should have the same value (in corresponding column) in main table in the output of the query.</p>
  
  <hr>
  
  <p>I tried creating below query</p>
  
  <pre><code>select t1.* from otherTable t1
  LEFT OUTER JOIN mainTable t2
  ON ( t1.c1 = t2.c1 OR t1.c2 = t2.c2 OR t1.c3 = t2.c3 OR t1.c4 = t2.c4 )
  Where t2.c5 is null;
  </code></pre>
  
  <p>However, hive throws below exception</p>
  
  <blockquote>
    <p>OR not supported in JOIN currently</p>
  </blockquote>
  
  <p>I understand the hive limitation and many time I have used <code>UNION (ALL | DISTINCT)</code> with inner join to overcome this limitation; but not able to use the same strategy with this.</p>
  
  <p>Please help.</p>
  
  <p><strong>EDIT 1</strong> : I have hive version restriction - Can only use version 1.2.0</p>