Subqueries in the FROM Clause

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...
   (Note: Only valid starting with Hive 0.13.0)

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. 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 subqueries.

The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).

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

...

As of Hive 0.13 some types of subqueries are supported in the WHERE clause. Those are queries where the result of the query can be treated as a constant for IN and NOT IN statements (called uncorrelated subqueries because the subquery does not reference columns from the parent query):

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

The other supported types are EXISTS and NOT EXISTS subqueries:

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

...