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:
-
You can use
and
to combine multiple conditions for a table join. -
It cannot be used with the keyword
update
. -
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 |