title: Hive - Use NOT Exists in Using Semi Join
body: <p>I need to use NOT IN query in Hive.</p>
  
  <p>I have 3 tables A, B and C. </p>
  
  <p>B with fields PRODUCT, ID and VALUE. 
  C with fields ID and VALUE.</p>
  
  <p>I need to write the rows from table B, which has no matching ID and VALUE fields in table C, to table A.</p>
  
  <p><code>INSERT OVERWRITE TABLE A a 
  SELECT *
  FROM B b
  LEFT SEMI JOIN C c ON (b.ID = c.ID AND b.VALUE = c.VALUE) where b.ID = NULL AND b.VALUE = NULL;</code></p>
  
  <p>This suggestion from <code>http://stackoverflow.com/questions/25041026/hive-left-semi-join-for-not-exists</code> is not working, as I referred the right side table in WHERE clause, which should not be done. </p>
  
  <p>How to form the equivalent query without referrring the right side table in the WHERE clause.</p>
  
  <p>Any other solution?</p>