Use of hive UNION all and hive subqueries

Use of Union

The union is used to combine the result set of multiple SELECT statements into a single result set. The Union ALL (Bag Union) is currently supported. Duplicate rows cannot be eliminated, and the number of columns returned by each SELECT statement must be the same as the name, otherwise a syntax error is thrown.

Select_statement UNION ALL select_statement UNION ALL select_statement .....

If you have to do some extra processing of the result set of the Union, the entire statement can be embedded in the FROM clause.

SELECT * FROM (
    select_statement
        UNION all
    select_statement
    ) unionresult

Hive subquery

Child query syntax

Select .... from (subquery) name ...  

Hive supports subqueries only in the FROM clause. A subquery must be given a name, because each table must have a name in the FROM clause. A subquery must have a unique name for the column of the query list. A subquery's query list is available on the outside of the query, just to the table column. Subqueries can also be a union query expression, and hive supports arbitrary levels of subqueries.

Example 1:

Select col from (
    select A+b as col from T1
    ) T2;

Example 2:

Select T3.col from (
    select A+b as col from T1
      UNION all
    select C+d as col from T2
    ) as T3;