Page History

...

The number and names of columns returned by each select_statement have to be the same. Otherwise, a schema error is thrown.

UNION within a FROM Clause

If some additional processing has to be done on the result of the UNION, the entire statement expression can be embedded in a FROM clause like below:

...

    SELECT u.id, actions.date
    FROM (
        SELECT av.uid AS uid 
        FROM action_video av 
        WHERE av.date = '2008-06-03' 
        UNION ALL 
        SELECT ac.uid AS uid 
        FROM action_comment ac 
        WHERE ac.date = '2008-06-03' 
     ) actions JOIN users u ON (u.id = actions.uid) 

Unions in DDL and Insert Statements

Unions can be used in views, inserts, and CTAS (create table as select) statements. A query can contain multiple UNION clauses, as shown in the syntax above.

Applying Subclauses

To apply ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

...

SELECT key FROM src
UNION
SELECT key FROM src1 
ORDER BY key LIMIT 10

Column Aliases for Schema Matching

UNION expects the same schema on both sides of the expression list. As a result, the following query may fail with an error message such as "FAILED: SemanticException 4:47 Schema of both sides of union should match."

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" FROM source_table_2

In such cases, column aliases can be used to force equal schemas:

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" as category FROM source_table_2

Column Type Conversion

Before HIVE-14251 in release 2.2.0, Hive tries to perform implicit conversion across Hive type groups. With the change of HIVE-14251, Hive will only perform implicit conversion within each type group including string group, number group or date group, not across groups. In order to union the types from different groups such as a string type and a date type, an explicit cast from string to date or from date to string is needed in the query.

  SELECT name, id, cast('2001-01-01' as date) d FROM source_table_1
  UNION ALL
  SELECT name, id, hiredate as d FROM source_table_2

Version Information

...