Skip to content

Join

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

Builds merged records from two source tables, optionally restricted to matching keys.

Join produces all permutations of records from the two input tables unless keys are supplied — when keys are given, the join is restricted to records whose key values match. The semantics mirror an SQL inner join, though the syntax differs.

By default each key pair is matched by equality. An optional comparison array (one operator per key pair) makes a join a range join: each pair is tested with its own operator and the whole set is AND-ed together, like an SQL ON clause. Operators are "=", ">", ">=", "<", "<=". Omitting the array means all "=" — exactly the equality behaviour above. Equality pairs drive the index lookup; comparison pairs are applied as filters on the matched candidates. A join with no "=" pair at all is a pure-range join (the right table is scanned and the comparisons do all the matching). The comparison sides must be field references — a constant bound is a filter, not a join condition, so it is not accepted here.

Multiple Join / LeftJoin rules can be chained by naming the result table (the optional trailing name parameter); subsequent joins reference that name as a regular @table.

Parameters

Cartesian (no keys)

1 — left Mandatory — accepts: table reference

2 — right Mandatory — accepts: table reference

Keyed

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 the same length as rightKey.

4 — rightKey Mandatory — accepts: array of fields

Key fields on the right table; must have the same length as leftKey.

Keyed and named

1 — left Mandatory — accepts: table reference

2 — right Mandatory — accepts: table reference

3 — leftKey Mandatory — accepts: array of fields

4 — rightKey Mandatory — accepts: array of fields

5 — name Mandatory — accepts: STRING

A name for the joined result table; can be referenced from later Join / LeftJoin rules.

Range (comparison operators)

1 — left Mandatory — accepts: table reference

2 — right Mandatory — accepts: table reference

3 — leftKey Mandatory — accepts: array of fields

4 — rightKey Mandatory — accepts: array of fields

5 — operators Mandatory — accepts: array of STRING

One comparator per key pair — "=", ">", ">=", "<", "<=". AND-ed together. Omit for all-equality.

6 — name Optional — accepts: STRING

Optional result-table name (must come after the operator array).

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.
  • Comparison operands must be fields (constants are filters, not joins).
  • The named result table must not already exist.

Examples

Join(@conditions.csv, @encounter.csv, [:encounter], [:id], "ce_temp")

Join(@encounter.csv, @conditions.csv)

Join(@observations.csv, @patients.csv, [:patient], [:id])

Range join — equality on one key, a comparison on another

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

Pure-range join — no equality key, value between two bounds

Join(@a.csv, @b.csv, [:lo, :hi], [:val, :val], ["<=", ">="])

See also

LeftJoin