Version:

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

Name Type Description
join_table_name string Name of the join table to be created. Has the same naming restrictions as tables.
table_names array of strings The list of table names composing the join. Corresponds to a SQL statement FROM clause.
column_names array of strings List 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.
expressions array of strings An 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 ( [] ).
options map of string to strings

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

Supported Parameters (keys) Parameter Description
collection_name Name of a collection which is to contain the join. If the collection provided is non-existent, the collection will be automatically created. If empty, then the join will be at the top level. The default value is ''.
max_query_dimensions Obsolete in GPUdb v7.0
optimize_lookups

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

  • true
  • false
ttl Sets the TTL of the join table specified in input parameter join_table_name.
view_id view this projection is part of. The default value is ''.
no_count return 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_size Maximum number of records per joined-chunk for this table. Defaults to the gpudb.conf file chunk size

Output Parameter Description

Name Type Description
join_table_name string Value of input parameter join_table_name.
count long The number of records in the join table filtered by the given select expression.
info map of string to strings Additional information.