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

Please 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