Create Join Table

Creates a table that is the result of a SQL JOIN.

For join details and examples see: Joins. For limitations, see Join Limitations and Cautions.

Input Parameter Description

NameTypeDescription
join_table_namestringName of the join 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 composing the join, each in [schema_name.]table_name format, using standard name resolution rules. Corresponds to a SQL statement FROM clause.
column_namesarray of stringsList of member table columns or column expressions to be included in the join. Columns can be prefixed with 'table_id.column_name', where 'table_id' is the table name or alias. Columns can be aliased via the syntax 'column_name as alias'. Wild cards '*' can be used to include all columns across member tables or 'table_id.*' for all of a single table's columns. Columns and column expressions composing the join must be uniquely named or aliased--therefore, the '*' wild card cannot be used if column names aren't unique across all tables.
expressionsarray of stringsAn optional list of expressions to combine and filter the joined tables. Corresponds to a SQL statement WHERE clause. For details see: expressions. The default value is an empty array ( [] ).
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 join_table_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified_join_table_name. The default value is false. The supported values are:

  • true
  • false
collection_name[DEPRECATED--please specify the containing schema for the join as part of input parameter join_table_name and use Create Schema to create the schema if non-existent] Name of a schema for the join. If the schema is non-existent, it will be automatically created. The default value is ''.
max_query_dimensionsNo longer used.
optimize_lookups

Use more memory to speed up the joining of tables. The default value is false. The supported values are:

  • true
  • false
strategy_definitionThe tier strategy for the table and its columns.
ttlSets the TTL of the join table specified in input parameter join_table_name.
view_idview this projection is part of. The default value is ''.
no_countReturn a count of 0 for the join table for logging and for Show Table ; optimization needed for large overlapped equi-join stencils. The default value is 'false'.
chunk_sizeMaximum number of records per joined-chunk for this table. Defaults to the gpudb.conf file chunk size
enable_virtual_chunkingCollect chunks with accumulated size less than chunk_size into a single chunk. The default value is 'false'.
enable_pk_equi_joinUse equi-join to do primary key joins rather than using primary-key-index

Output Parameter Description

NameTypeDescription
join_table_namestringValue of input parameter join_table_name.
countlongThe number of records in the join table filtered by the given select expression.
infomap of string to strings

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

Possible Parameters (keys)Parameter Description
qualified_join_table_nameThe fully qualified name of the join table (i.e. including the schema)