Skip to content

LeftJoin

table category: table-control no `||` resultSize: 1

Like Join, but always emits every record from the left table even when no key match is found on the right.

LeftJoin requires keys for both tables (unlike Join, which can run un-keyed). The left table's records are always preserved; right-table fields are filled in only where a key match exists.

An optional comparison array (one operator per key pair — "=", ">", ">=", "<", "<=", AND-ed together) turns this into a range left join, exactly as for Join. Every left record is still preserved; the comparisons decide which right records (if any) match. Omit the array for all-equality.

Parameters

1 — left Mandatory — accepts: table reference

2 — right Mandatory — accepts: table reference

3 — leftKey Mandatory — accepts: array of fields

Key fields on the left table. Must have at least one entry and the same length as rightKey.

4 — rightKey Mandatory — accepts: array of fields

Key fields on the right table.

5 — operators Optional — accepts: array of STRING

Optional comparator per key pair ("=", ">", ">=", "<", "<="). Omit for all-equality.

6 — name Optional — accepts: STRING

A name for the joined result table (after the operator array, if present); can be referenced from later Join / LeftJoin rules.

Requires

  • All referenced tables and fields must exist.
  • The same number of keys must exist for both tables.
  • The operator array, if present, must have one entry per key pair.
  • Key fields on the two sides must have matching types.
  • At least one key must be supplied for each table.
  • Comparison operands must be fields (constants are filters, not joins).
  • The named result table must not already exist.

Examples

LeftJoin(@patients.csv, @conditions.csv, [:id, :deathdate], [:patient, :start], "pc_temp")

LeftJoin(@patients.csv, @conditions.csv, [:id], [:patient])

Range left join — keep every left row, comparison decides matches

LeftJoin(@a.csv, @b.csv, [:k, :date], [:bk, :date], ["=", ">"])

See also

Join