union join

Syntax

uj(leftTable, rightTable, matchingCols, [rightMatchingCols])

Details

Performs a union join on two regular in-memory tables based on the specified columns and returns a merged in-memory table.

This function is compatible with kdb+ uj:

• It uses the left table as the base and performs equality matching based on matchingCols.

• Matched records are updated by the right table: values in non-matching columns from the right table overwrite the corresponding columns in the left table. Records in the right table that do not find a match are appended to the end of the result table.

• The result table contains the union of columns from both tables, with missing fields filled with null values.

• When matchingCols and rightMatchingCols are not identical, the result table retains only matchingCols (i.e., the matching columns from the left table).

Note: This function currently supports only regular in-memory tables. It does not support DFS tables, partitioned tables, stream tables, or other table objects.

Parameters

leftTable is a regular in-memory table specifying the left table.

rightTable is a regular in-memory table specifying the right table.

matchingCols is a STRING scalar or vector specifying the matching column names in the left table. The specified columns must exist in leftTable. If multiple column names are specified, they jointly form the matching key.

rightMatchingCols (optional) is a STRING scalar or vector specifying the matching column names in the right table. It must be specified when the matching column names differ between the left and right tables. The specified columns must exist in rightTable, and its length must be the same as matchingCols. If omitted, the same column names as matchingCols are used to match in the right table by default.

Note:

• The keys formed by matchingCols in leftTable must be unique, and the keys formed by rightMatchingCols in rightTable must also be unique. Each key can appear in only one row within the same table. If rightMatchingCols is not specified, the keys formed by matchingCols in rightTable must be unique.

• NULL handling in matchingCols is controlled by enableNullSafeJoin. By default, enableNullSafeJoin=false, and null values do not match. After setting it to true, null values are considered equal for matching.

Returns

A regular in-memory table.

Examples

Example 1: The right table overwrites existing records in the left table, with a single matching column and identical column names in both tables.

t1 = table(`a`b as sym, 10 20 as px)
t2 = table(`b`c as sym, 99 30 as px)
uj(leftTable=t1, rightTable=t2, matchingCols="sym")

Example 2: The values in the matching column of the right table differ from those in the left table.

t1 = table(`a`b as sym, 10 20 as px)
t2 = table(`c as sym, 300 as qty)
uj(leftTable=t1, rightTable=t2, matchingCols="sym")

Example 3: By default, NULL values in the matching column do not match.

t1 = table(int([NULL, 1]) as id, 10 20 as v)
t2 = table(int([NULL, 2]) as id, 99 30 as v)
uj(leftTable=t1, rightTable=t2, matchingCols="id")

When enableNullSafeJoin is enabled, null values in the matching columns are considered equal.

setDynamicConfig("enableNullSafeJoin", true)
uj(leftTable=t1, rightTable=t2, matchingCols="id")

Example 4: The column names in the left and right tables do not match.

t1 = table(1 2 as id, 10 20 as px)
t2 = table(2 3 as rid, 99 30 as px)
uj(leftTable=t1, rightTable=t2, matchingCols="id", rightMatchingCols="rid")

Example 5: Multiple matching columns are specified.

t1 = table(
    `AAPL`AAPL`MSFT`MSFT as sym,
    `NASDAQ`NYSE`NASDAQ`NYSE as exch,
    180.5 181.0 320.1 321.0 as px,
    1000 1200 800 900 as vol
)
t2 = table(
    `AAPL`MSFT`GOOG`MSFT as sym,
    `NASDAQ`NASDAQ`NASDAQ`CBOE as exch,
    182.0 319.5 140.0 322.0 as px,
    1500 850 600 950 as vol
)

uj(
    leftTable=t1,
    rightTable=t2,
    matchingCols=["sym", "exch"]
)