Both left and right aliases encountered in JOIN '0' In Hive : Operator With Left Join in Hive

Question

I am joining 2 tables and using <= in Join Condition also applying one filter condition so that it can only fetch remaining data from left table where filter condition is true.

I am using below query.

SELECT * FROM  test.TABLE1 T1 
left join test.TABLE2 T2 
on (
T1.low<=T2.low
) 
where  t1.ID='1';

Error:

 Error while compiling statement: FAILED: SemanticException 
[Error 10017]: Line 4:0 Both left and right aliases encountered in JOIN '0' (state=42000,code=10017)

When I am only giving '=' condition instead of <= then its running without any issue.

Answer 1

You could do it like this:

with data as ( Select * FROM test.TABLE1 t1 WHERE t1.ID='1')
Select * 
FROM data T1
LEFT JOIN test.TABLE2 T2 on T1.low<=T2.low