Using a subquery
Hive supports subqueries in FROM clauses and WHERE clauses that you can use for many Apache 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.