How can I replace the OR condition in HIVE Join

Question

I have the below 2 tables

Employee(age,name,dpt_cd,dpt_rg_cd)
Department(dpt_id,dpt_cd,dpt_rg_cd)

I want to perform the below query on these tables, but unfortunately HIVE doesn't support OR in JOIN condition. How can I rewrite the query without OR condition which gives the same result

SELECT * FROM employee LEFT OUTER JOIN department ON (employee.dpt_cd =department.dpt_cd OR (employee.dpt_cd ='' AND employee.dpt_rg_cd= employee.dpt_rg_cd ))  

Answer 1

You can re-write the query as two selects with a union all as follows:

select * from employee left outer join department on (employee.dpt_cd =department.dpt_cd)
union all
select * from employee left outer join department on (employee.dpt_rg_cd = employee.dpt_rg_cd) where employee.dpt_cd ='';

This may be a very slow query but should produce the result you want.

Answer 2

To solve multiple equerry issue in hive use semi left join ie

select x.*
from employee x
LEFT SEMI JOIN  department   b on (x.buyer_id= b.id )
LEFT SEMI JOIN  department   c on (x.seller_id= c.id )

Answer 3

Simply use your conditions in the where clause and put 1=1 in on clause. Like the following:

SELECT * FROM employee LEFT OUTER JOIN department ON ( 1=1) 
where employee.dpt_cd =department.dpt_cd OR 
      (employee.dpt_cd ='' AND employee.dpt_rg_cd= employee.dpt_rg_cd )