join

Operator

  join — combine data from two inputs using a join predicate

Synopsis

<left-input>
| [anti|inner|left|right] join (
  <right-input>
) [as { <left-name>,<right-name> }] [on <predicate>]

( => <left-input> => <right-input> )
| [anti|inner|left|right] join [as { <left-name>,<right-name> }] [on <predicate>]

==========
DEPRECATED
==========
( => <left-input> => <right-input> )
| [anti|inner|left|right] join on <left-key>=<right-key> [[<field>:=]<right-expr>, ...]

Description

The join operator combines values from two inputs according to the Boolean-valued <predicate>. Logically, a cross product of all values is formed by taking each value L from <left-input> and forming records with all of the values R from the <right-input> of the form {<left-name>:L,<right-name>:R}. The result of the join is the set of all such records that satisfy <predicate>.

The output order of the resulting records is undefined.

If the “as clause” is ommited, then <left-name> defaults to “left” and <right-name> defaults to “right”.

If <predicate> is ommited, then it is presumed true and the entire cross product is produced.

The available join types are:

• inner - as described above
• left - the inner join plus a set of single-field records of the form {<left-name>:L} for each value L in <left-input> absent from the inner join
• right - the inner join plus a set of single-field records of the form {<right-name>:R} for each value R in <right-input> absent from the inner join
• anti - the set of records of the form {<left-name>:L} for which there is no value R in <right-input> where the combined record {<left-name>:L,<right-name>:R} satisfies <predicate>

Examples

Join some numbers

join (from (values 1,3)) on left=right | sort

1
2
3

Loading...

echo '1
2
3' \
| super -s -c 'join (from (values 1,3)) on left=right | sort' -

Join some records with scalar keys

join (from (values "foo","baz")) as {recs,key} on key=recs.key | values recs.value | sort

{key:"foo",value:1}
{key:"bar",value:2}
{key:"baz",value:3}

Loading...

echo '{key:"foo",value:1}
{key:"bar",value:2}
{key:"baz",value:3}' \
| super -s -c 'join (from (values "foo","baz")) as {recs,key} on key=recs.key | values recs.value | sort' -

Join some records requiring a cross-product calculation

# spq
join (from (values {id:"apple"},{id:"chair"},{id:"car"})) as {b,a} on grep("a", a.id) and grep("b", b.key) | sort
# input
{key:"foo",value:1}
{key:"bar",value:2}
{key:"baz",value:3}
# expected output
{b:{key:"bar",value:2},a:{id:"apple"}}
{b:{key:"bar",value:2},a:{id:"car"}}
{b:{key:"baz",value:3},a:{id:"apple"}}
{b:{key:"baz",value:3},a:{id:"car"}}

Anti-join some numbers

anti join (from (values 1,3)) on left=right | sort

1
2
3

Loading...

echo '1
2
3' \
| super -s -c 'anti join (from (values 1,3)) on left=right | sort' -

The join tutorial includes several more examples.