Skip to main content

DB_Table.join

joinrightjoin_kind on right_prefixon_problems

Group: Calculations

Documentation​

Joins two tables according to the specified join conditions.

  • If a column name cannot be found, a No_Such_Column is reported and an empty result is reported. - If a column index is invalid, an Index_Out_Of_Bounds is reported and an empty result is reported. - If there are column names that are clashing between the two tables, a Duplicate_Output_Column_Names is reported and the columns from the table are renamed as described below. - If a join condition correlates columns whose types are not compatible (for example comparing numeric types with text), an Invalid_Value_Type is reported. - If decimal columns are joined on equality, a Floating_Point_Equality is reported. In any of the above cases, if a problem occurs, the resulting table will have the desired structure, but it will be empty to indicate that the join has failed due to an erroneous join condition.

Arguments​

  • right: The table to join with.
  • join_kind: The Join_Kind for the joining the two tables. It defaults to Left_Outer.
  • on: A single condition or a common column name, or a list thereof, on which to correlate rows from the two tables. If multiple conditions are supplied, rows are correlated only if all are true. If common column names are provided, these columns should be present in both tables and an equality condition is added for each of them. By default, the join is performed on the first column of the left table correlated with a column in the right table with the same name.
  • right_prefix: The prefix added to right table column names in case of name conflict.
  • on_problems: Specifies how to handle problems if they occur, reporting them as warnings by default.

Remarks​

Column Renaming​

If columns from the two tables have colliding names, a prefix (by default Right_) is added to the name of the column from the right table. The left column remains unchanged. It is possible that the new name will be in use, in this case it will be resolved using the normal renaming strategy - adding subsequent _1, _2 etc.

Row Ordering For In-Memory Tables​

This operation requires a well-defined order of rows in the input tables. In-memory tables rely on the ordering stemming directly from their layout in memory. Database tables may not impose a deterministic ordering. If the table defines a primary key, it is used to by default to ensure deterministic ordering. That can be overridden by specifying a different ordering using Table.sort. If no primary key was defined nor any ordering was specified explicitly by the user, the order of columns is undefined and the operation will fail, reporting a Undefined_Column_Order problem and returning an empty table.

Row Ordering For Database Tables​

The ordering of rows in the resulting table is not specified.

Joining on equality of columns with the same name​

When performing an Inner join on two columns with the same name and an equality condition, only one copy of column will be included in the output (as these two columns would have the exact same content, so they would be redundant).

Same-name column join shorthand​

As a shorthand, providing a column name or a list of column names allows to join the two tables on equality of corresponding columns with the same name. So table.join other on=["A", "B"] is a shorthand for: table.join other on=[Join_Condition.Equals "A" "A", Join_Condition.Equals "B" "B"]