CODEWITHSUNDEEP
hivehiveqlimpalabigdata
user7328756
user7328756

Reputation: 11

How can I replace the OR condition in HIVE Join

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 ))

Upvotes: 1

Views: 4663

Answers (3)

Puneet Gupta
Puneet Gupta

Reputation: 31

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 )

Upvotes: 3

sandeep rawat
sandeep rawat

Reputation: 4957

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 )

Upvotes: 2

Finbarr O'B
Finbarr O'B

Reputation: 1464

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.

Upvotes: 1

Related Questions