How to write a non-equi join in Hive

Question

My tables include 3 date columns, dateA , dateB and dateC What I require:

JOIN ON dateA between dateB and dateC

The JOIN works quite well in Teradata, but I'm getting errors while running in Hive.

select *
from
table A
left join table B
on A.col1 = B.col1
and A.dateA between B.dateB and B.dateC

Error:

Both left and right aliases encountered in JOIN dateB

I would really appreciate some help on this!

Answer 1

The reason is Hive does not support non-equi joins. Move the BETWEEN join condition to the WHERE

select *
from
table A
left join table B on A.col1 = B.col1
WHERE (A.dateA between B.dateB and B.dateC)
      OR B.col1 is NULL --allow not joined records from A (left join)