`join` for filters

Join two tables.

Signature

> join {flags} (right-table) (left-on) (right-on)

Flags

--prefix, -p {string}: Prefix columns from the right table with this string (excluding the shared join key).
--suffix, -s {string}: Suffix columns from the right table with this string (excluding the shared join key).
--inner, -i: Inner join (default).
--left, -l: Left-outer join.
--right, -r: Right-outer join.
--outer, -o: Outer join.
--anti, -a: Anti join: keep rows with no match in right.

Parameters

right-table: The right table in the join.
left-on: Name of column in input (left) table to join on.
right-on: Name of column in right table to join on. Defaults to same column as left table.

Input/output types:

input	output
table	table

Examples

Join two tables

> [{a: 1 b: 2}] | join [{a: 1 c: 3}] a
╭───┬───┬───┬───╮
│ # │ a │ b │ c │
├───┼───┼───┼───┤
│ 0 │ 1 │ 2 │ 3 │
╰───┴───┴───┴───╯

Join multiple tables with distinct suffixes for the right table's columns

> [{id: 1 x: 10}] | join --suffix _a [{id: 1 x: 20}] id | join --suffix _b [{id: 1 x: 30}] id
╭───┬────┬────┬─────┬─────╮
│ # │ id │ x  │ x_a │ x_b │
├───┼────┼────┼─────┼─────┤
│ 0 │  1 │ 10 │  20 │  30 │
╰───┴────┴────┴─────┴─────╯

Join multiple tables with a prefix for the right table's columns

> [{id: 1 x: 10}] | join --prefix r_ [{id: 1 x: 20}] id
╭───┬────┬────┬─────╮
│ # │ id │ x  │ r_x │
├───┼────┼────┼─────┤
│ 0 │  1 │ 10 │  20 │
╰───┴────┴────┴─────╯

Pick a subset of rows without a matching row in the right table

> [{id: 1 x: 10}, {id: 2 x: 9}] | join --anti [{id: 1}] id
╭───┬────┬───╮
│ # │ id │ x │
├───┼────┼───┤
│ 0 │  2 │ 9 │
╰───┴────┴───╯

Left join a table whose rows all lack the join column by adding the column first

> [{id: 1 x: 10}] | join --left ([{y: 20}] | default null id) id
╭───┬────┬────┬───╮
│ # │ id │ x  │ y │
├───┼────┼────┼───┤
│ 0 │  1 │ 10 │   │
╰───┴────┴────┴───╯

If the input table or the right table has rows but none of them has its join column (left-on for the input, right-on for the right table), the join fails with a column-not-found error. This applies to every join type, including --left, --right, --outer and --anti. An empty table passes this check. In a table where only some rows have the join column, the other rows join as non-matching rows. To join a table whose rows all lack the column, add it first, for example with default null <column>.