...
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.
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:
| Code Block |
|---|
(SELECT key FROM src ORDER BY key LIMIT 10)
UNION
(SELECT key FROM src1 ORDER BY key LIMIT 10) |
To use an ORDER BY, SORT BY, CLUSTER BY or LIMIT clause to the entire UNION result, parenthesize the individual SELECT statements and 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:
| Code Block |
|---|
(SELECT key FROM src)
UNION
(SELECT key FROM src1)
ORDER BY key LIMIT 10 |
A statement without parentheses is equivalent to one parenthesized as just shown.
| Info | ||
|---|---|---|
| ||
In Hive 0.12.0 and earlier releases, unions can only be used within a subquery such as "SELECT * FROM (select_statement UNION ALL select_statement UNION ALL ...) unionResult". As of Hive 0.13.0, unions can also be used in a top-level query: "select_statement UNION ALL select_statement UNION ALL ...". (See HIVE-6189.) Before Hive 1.2.0, only UNION ALL (bag union) is supported, in which duplicates are not eliminated. UNION (or UNION DISTINCT) is supported since Hive 1.2.0. (See HIVE-9039) |
...