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

Question

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?

Have you tried BETWEEN? And which version of Hive are you using? — Ramesh, Nov 20 '17 at 19:11

leftjoin · Accepted Answer · 2017-11-20T20:09:48.473

2

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

edited Nov 20 '17 at 20:09

answered Nov 20 '17 at 19:03

leftjoin

36,950
8
57
116

2

You need to add an `OR table2.id is null` to your where clause. Otherwise you end up with an inner join. – Andrew Nov 20 '17 at 20:02
@ Andrew. Thanks, forgot about it. fixed – leftjoin Nov 20 '17 at 20:10

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

1 Answers1