join

join joins data from a pair of tables - a left and right table - based upon a set of match columns (columnsToMatch). The match columns establish key identifiers in the left table that will be used to find data in the right table. Any data types can be chosen as keys, and keys can be constructed from multiple values.

The output table contains rows that have matching values in both tables. Rows that do not have matching criteria will not be included in the result. If there are multiple matches between a row from the left table and rows from the right table, all matching combinations will be included. If no columnsToMatch are specified (""), every combination of left and right table rows is included.

Syntax

Parameters

ParameterTypeDescription
rightTableTable

The table data is added from.

columnsToMatchString

Columns from the left and right tables used to join on.

  • "A = B" will join when column A from the left table matches column B from the right table.
  • "X" will join on column X from both the left and right table. Equivalent to "X = X".
  • "X, A = B" will join when column X matches from both the left and right tables, and when column A from the left table matches column B from the right table.
  • If this argument is left empty (""), a cross-join is performed. The result is a table with every possible combination of rows from the two tables.
columnsToAddString

The columns from the right table to be added to the left table based on key.

  • If this argument is omitted or left empty (""), all columns from the right table are added to the left table, except right table columns whose names match a left table column in columnsToMatch.
  • "X" will add column X from the right table to the left table as column X.
  • "Y = X" will add column X from right table to left table and rename it to be Y.
reserveBitsint

The number of bits of each result row key to reserve for the right table rows that match a left table row, between 1 and 62 (inclusive). If omitted, 10 bits are reserved unless configured otherwise. Reserving enough bits for the largest group of matching right table rows avoids shifting result rows as groups grow. If the maximum size of a right table's group is small, reserve fewer bits by setting reserveBits on initialization. A join whose result can never change, such as a join of two static tables, ignores reserveBits and uses just enough bits for its right rows.

Returns

A new table containing rows that have matching values in both tables. Rows that do not have matching criteria will not be included in the result. If there are multiple matches between a row from the left table and rows from the right table, all matching combinations will be included. If no columnsToMatch are specified (""), every combination of left and right table rows is included.

Examples

In the following example, the left and right tables are joined on a matching column named DeptID.

The left table has seven rows of data and the right table has four rows of data, but the result table has five rows. This is because the last two rows of the left table and the last row of the right table have no matches in the DeptID column, so they are not included in the resulting table.

If the right table has columns that need renaming due to an initial name match, a new column name can be supplied in the third argument of the join. In the following example, Telephone is renamed to DeptTelephone.

In the following example, the left and right tables have multiple matches. The result is the cross product of possible outcomes.

In some cases, the matching columns have different names in the left and right table. Below, the left table has a column name DeptNumber that we want to match to the column DeptID in the right table. To perform this match, the second argument needs the name of each column in the left and right tables.

This example is identical to the previous one, but sets the reserveBits parameter and passes columnsToMatch and columnsToAdd as collections of JoinMatch and JoinAddition. Because both tables in this example are static, the engine sizes the result from the actual right table groups, and reserveBits does not change the result. The setting matters when either table is refreshing.

In some cases, the matching columns argument is absent. As a result all possible matches are joined.