Create Union

Merges data from one or more tables with comparable data types into a new table.

The following merges are supported:

UNION (DISTINCT/ALL) - For data set union details and examples, see Union. For limitations, see Union Limitations and Cautions.

INTERSECT (DISTINCT/ALL) - For data set intersection details and examples, see Intersect. For limitations, see Intersect Limitations.

EXCEPT (DISTINCT/ALL) - For data set subtraction details and examples, see Except. For limitations, see Except Limitations.

MERGE VIEWS - For a given set of filtered views on a single table, creates a single filtered view containing all of the unique records across all of the given filtered data sets.

Non-charN 'string' and 'bytes' column types cannot be merged, nor can columns marked as store-only.

Input Parameter Description

NameTypeDescription
table_namestringName of the table to be created, in [schema_name.]table_name format, using standard name resolution rules and meeting table naming criteria.
table_namesarray of stringsThe list of table names to merge, in [schema_name.]table_name format, using standard name resolution rules. Must contain the names of one or more existing tables.
input_column_namesarray of arrays of stringsThe list of columns from each of the corresponding input tables.
output_column_namesarray of stringsThe list of names of the columns to be stored in the output table.
optionsmap of string to strings

Optional parameters. The default value is an empty map ( {} ).

Supported Parameters (keys)Parameter Description
create_temp_table

If true, a unique temporary table name will be generated in the sys_temp schema and used in place of input parameter table_name. If persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified_table_name. The default value is false. The supported values are:

  • true
  • false
collection_name[DEPRECATED--please specify the containing schema for the projection as part of input parameter table_name and use Create Schema to create the schema if non-existent] Name of the schema for the output table. If the schema provided is non-existent, it will be automatically created. The default value is ''.
mode

If merge_views, then this operation will merge the provided views. All input parameter table_names must be views from the same underlying base table. The default value is union_all.

Supported ValuesDescription
union_allRetains all rows from the specified tables.
unionRetains all unique rows from the specified tables (synonym for union_distinct).
union_distinctRetains all unique rows from the specified tables.
exceptRetains all unique rows from the first table that do not appear in the second table (only works on 2 tables).
except_allRetains all rows(including duplicates) from the first table that do not appear in the second table (only works on 2 tables).
intersectRetains all unique rows that appear in both of the specified tables (only works on 2 tables).
intersect_allRetains all rows(including duplicates) that appear in both of the specified tables (only works on 2 tables).
merge_viewsMerge two or more views (or views of views) of the same base data set into a new view. If this mode is selected input parameter input_column_names AND input parameter output_column_names must be empty. The resulting view would match the results of a SQL OR operation, e.g., if filter 1 creates a view using the expression 'x = 20' and filter 2 creates a view using the expression 'x <= 10', then the merge views operation creates a new view using the expression 'x = 20 OR x <= 10'.
chunk_sizeIndicates the number of records per chunk to be used for this output table.
create_indexesComma-separated list of columns on which to create indexes on the output table. The columns specified must be present in input parameter output_column_names.
ttlSets the TTL of the output table specified in input parameter table_name.
persist

If true, then the output table specified in input parameter table_name will be persisted and will not expire unless a ttl is specified. If false, then the output table will be an in-memory table and will expire unless a ttl is specified otherwise. The default value is false. The supported values are:

  • true
  • false
view_idID of view of which this output table is a member. The default value is ''.
force_replicated

If true, then the output table specified in input parameter table_name will be replicated even if the source tables are not. The default value is false. The supported values are:

  • true
  • false
strategy_definitionThe tier strategy for the table and its columns.

Output Parameter Description

NameTypeDescription
table_namestringValue of input parameter table_name.
infomap of string to strings

Additional information. The default value is an empty map ( {} ).

Possible Parameters (keys)Parameter Description
countNumber of records in the final table
qualified_table_nameThe fully qualified name of the result table (i.e. including the schema)