Page History

...

• LEFT SEMI JOIN implements the correlated uncorrelated IN/EXISTS subquery semantics in an efficient way. Since Hive currently does not support IN/EXISTS subqueries, you can rewrite your queries using LEFT SEMI JOIN. The restrictions of using LEFT SEMI JOIN is that the right-hand-side table should only be referenced in the join condition (ON-clause), but not in WHERE- or SELECT-clauses etc.

  Code Block
  SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

  can be rewritten to:

  Code Block
  SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

...