Hive supports subqueries only in the FROM clause (through Hive 0.12). The subquery has to be given a name because every table in a FROM clause must have a name. Columns in the subquery select list must have unique names.

What is the benefit of using sub queries in hive?

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. A subquery is a SQL expression in an inner query that returns a result set to the outer query.

Why is subquery bad?

One of the biggest places where using subqueries can create a significant problem is in the case of correlated subqueries. Correlated subqueries execute once for each row returned by the parent query. As such, they can very quickly become performance hogs when the parent returns a large number of rows.