"Hive" subquery

The subquery is not supported in hive

This does not imply that it does not support in or not

In or not in the back is fixed value words are supported

But it can be customized.

For example

Select ID from table not in (all-in-a-

But this is not supported.

Select ID from table1 not in (

Select ID from table2 where col1 = ' a '

)

We need to use the left join to implement

(1) Connect the Table1 and table2 to the eligible data

Select T1.id as id1,t2.id as Id2 from table1 T1 left join (

Select ID from table2 where col1 = ' a '

) t2 on t1.id = t2.id

At this point the Table2 entry for the eligible Table1 entry is null

(2) then filter the data according to in or not

where

IN:ID2 is not NULL

Not IN:ID2 is null

Or use the left semi-join (not supported by right Semi-join)

Left-semi join returns the record on the left table that satisfies the on condition

Select ID from table1 T1 left semi join table2 T2

On t1.id = T2.id and t2.col1! = ' a '

Left-semin join is more efficient than join, because one of the established records in the left table will stop matching on the right table once it is matched to the right table.

"Hive" subquery