Search code examples
joinhiveleft-joinhiveqlcoalesce

Hive-How to join tables with OR clause in ON statement

I've got the following problem. In my oracle db I have query as follows:

select * from table1 t1
inner join table2 t2 on 
(t1.id_1= t2.id_1 or t1.id_2 = t2.id_2)

and it works perfectly. Nowadays I need to re-write query on hive. I've seen that OR clause doesn't work in JOINS in hive (error warning : 'OR not supported in JOIN'). Is there any workaround for this except splitting query between two separate and union them?

Solution

  • Another way is to union two joins, e.g.,

    select * from table1 t1
inner join table2 t2 on 
(t1.id_1= t2.id_1)
union all
select * from table1 t1
inner join table2 t2 on 
(t1.id_2 = t2.id_2)