Search code examples
joinhiveleft-joinhiveqlnon-equi-join

What is alternative to performing a left join on GTE or LTE conditions?

I am required to use a specific old version of HIVE that prevents me from joining 2 tables on GTE or LTE conditions. For example what is the equivalent of

select *
from table1 as t1
left join table2 as t2
on t1.id = t2.id
  and (t1.date >= t2.date and t1.date <= t2.date+7) -- i can no longer do this condition

What is an alternative query to this?

Solution

  • The alternative is to move GTE/LTE part of condition to the where clause which will be applied after join as a filter:

    select *
  from table1 as t1
       left join table2 as t2 on t1.id = t2.id
 where (t1.date >= t2.date and t1.date <= date_add(t2.date,7)) 
       or t2.id is null