full 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. Currently, it is not supported to join more than two tables at once.
2. Table aliases are not supported for table joins.
3. You can use and to combine multiple conditions for a table join.
4. It cannot be used with the keyword update.
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 |