...
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:
...
Code Block |
---|
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:
...
Code Block |
---|
SELECT key FROM src UNION SELECT key FROM src1 ORDER BY key LIMIT 10 |
Column Aliases for UNION ALL
UNION ALL expects the same schema on both sides of the expression list. As a result, the following query When a specific column value is given in one or more SELECT statements (but not in all of them), the UNION may fail with an error message such as "FAILED: SemanticException 4:47 Schema of both sides of union should match." Column aliases should be used for such values, for example
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
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 |
...