Use a subquery
Hive supports subqueries in FROM clauses and WHERE clauses that you can use for many Hive operations, such as filtering data from one table based on contents of another table.
Select all the state and net_payments values from the transfer_payments table if the value of the year column in the table matches a year in the us_census table.
SELECT state, net_payments
FROM transfer_payments
WHERE transfer_payments.year IN (SELECT year FROM us_census);
The predicate starts with the first WHERE keyword. The predicate operator is the IN
keyword.
The predicate returns true for a row in the transfer_payments table if the year
value in at least one row of the us_census table matches a year value in the
transfer_payments table.