Hive subquery in where clause (Select * from table 1 where dt > (Select max(dt) from table2) )..please suggest an alternative

Question

I am looking for something in hive like

Select * from table 1 where dt > (Select max(dt) from table2) 

Obviously hive doesn't support sub queries in where clause and also, even if I use joins or semi join, it compares only = and not > (As far as I know).

Can some one please suggest me an alternative solution to write the same query in hive?

Answer 1

select table_1.* from table_1
join (select max(dt) as max_dt from table2) t2
where table_1.dt > t2.max_dt

You're right, you can only have equality conditions in the join on clause, but you can have whatever you want in the where clause.

Usually this isn't recommended, since not having an on clause means Hive will first do a full cartesian product, then filter, but since one side of the join only has one row, that's not an issue here.