full join/full outer join

Syntax

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

Arguments

leftTable and rightTable are the tables to be joined.

matchingCols a string scalar/vector indicating matching columns.

rightMatchingCols a string scalar/vector indicating all the matching columns in rightTable . This optional argument must be specified if at least one of the matching columns has different names in leftTable and rightTable . The joining column names in the result will be the joining column names from the left table

SQL-compatible syntax

select column_name(s)
from leftTable full join rightTable
on leftTable.matchingCol=rightTable.rightMatchingCol

Note:

  1. You can use and to combine multiple conditions for a table join.

  2. It cannot be used with the keyword update.

  3. If the left table is not a DFS table, its right table cannot be a DFS table either.

Details

Return all rows from equi join together with rows that are not matched from either the left table or the right table.

Examples

t1= table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value);
t1;
id value
1 7.8
2 4.6
3 5.1
3 0.1
t2 = table(5 3 1 as id,  300 500 800 as qty);
t2;
id qty
5 300
3 500
1 800
fj(t1, t2, `id);
id value t2_id qty
1 7.8 1 800
2 4.6
3 5.1 3 500
3 0.1 3 500
5 300
select * from fj(t1, t2, `id) where id=3;
//equivalent to select * from t1 full join t2 on t1.id=t2.id where id=3
id value t2_id qty
3 5.1 3 500
3 0.1 3 500