LanguageManual SubQueries

Subquery Syntax

SELECT ... FROM (subquery) name ...

Hive supports subqueries only in the FROM clause. 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. The columns in the subquery select list are available in the outer query just like columns of a table. The subquery can also be a query expression with UNION. Hive supports arbitrary levels of sub-queries.

Example with simple subquery:

SELECT col 
FROM (
  SELECT a+b AS col
  FROM t1
) t2

Example with subquery containing a UNION ALL:

SELECT t3.col
FROM (
  SELECT a+b AS col
  FROM t1
  UNION ALL
  SELECT c+d AS col
  FROM t2
) t3