Page History

...

UNION is used to combine the result from multiple SELECT statements into a single result set.

...

• Hive versions prior to 1.2.0 only support UNION ALL (bag union), in which duplicate rows are not eliminated.
• In Hive 1.2.0 and later, the default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT

...

•  keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

...

You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

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

...

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

...

SELECT key FROM (SELECT key FROM src ORDER BY key LIMIT 10)a
UNION
SELECT key FROM (SELECT key FROM src1 ORDER BY key LIMIT 10)b

To use apply an ORDER BY, SORT BY, CLUSTER BY or LIMIT clause to the entire UNION result, place the ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY or LIMIT after the last one. The following example uses both ORDER BY and LIMIT clauses:

...