How to refactor AND and OR in a left outer join as hive doesn't support or in ON clause

0
votes

I'm running a hive left outer join query which involves AND and OR in ON clause. Hive doesn't support OR in ON clause. How do I rewrite this to run in Hive? If UNION is one of the answer, please note that the version of hive I'm using doesn't support UNION. Supports UNION ALL only. 

select 
*
from
a
left outer join b on a.a1=b.b1
left outer join c on c.c10=a.15 and (c.12=a.25 or c.13=a.25)
left outer join d on d.d1=a.a5
where
?
1
sqlhiveleft-join
can you just move (c.12=a.25 or c.13=a.25) to where clause? - Vladimir Semashkin
That was my first try. Didn't yield intended results. In fact no results. It works for inner join. Not for left outer - Cool Bindaas

1 Answers

0
votes

You could combine UNION ALL with DISTINCT:

SELECT DISTINCT *
FROM (
  select *
  from a
  left outer join b on a.a1=b.b1
  left outer join c on c.c10=a.15 and c.12=a.25 
  left outer join d on d.d1=a.a5
  where ?
  UNION ALL
  select *
  from a
  left outer join b on a.a1=b.b1
  left outer join c on c.c10=a.15 and c.13=a.25
  left outer join d on d.d1=a.a5
  where ?
) s