Description:
Join multiple sequences together.
Syntax:
join(Ai:Fi,xj,..;…)
Note:
The function joins multiple sequences of Ai according to the condition that the value of relational field/expression xj is equal to x1, and generates a table sequence whose fields are Fi,…. which reference the records of the original record sequences Ai. If xj is omitted, then use the key of Ai. An omitted xj won’t be matched. No matter how many record sequences are mutually related, the equivalence determination is conducted according to the x1 in A1. Therefore this is a one-to-many relationship.
Parameters:
Fi |
Field name of the resulting table sequence |
Ai |
Sequences or record sequences to be joined |
xj |
Relational field/ expression |
Options:
@f |
Full join;if no matching records are found, then use nulls to correspond |
@1 |
Left join; it is the number "1" instead of the letter "l" |
@m |
If all Ai are ordered against xj, then use merge operation to compute |
@p |
Perform a join according to positions while ignoring parameter xj |
@x |
If field values of the sequences to be joined are records, the joining values will be unfolded |
Return value:
A new table sequence whose fields are all referencing ones
Example:
|
A |
|
||
1 |
=demo.query("select top 3 EID,NAME from EMPLOYEE").keys(EID) |
|
||
2 |
=demo.query("select top 3 EID,NAME from FAMILY").keys(EID) |
|
||
3 |
=join(A1:Employee,EID;A2:Familymembers,EID) |
Normal
join. The non-matching items
will be discarded. Every field is a ref field pointing to the
corresponding
record in the original table
sequence |
||
4 |
=join@f(A1:Employee,EID;A2:Familymembers,EID) |
|
||
5 |
=join@1(A1:Employee,EID;A2:Familymembers,EID) |
Left
join. Take the first table sequence as the basis, and use nulls if no matching items are found |
||
6 |
=join@m(A1:Employee,EID;A2:Familymembers,EID) |
If all the relational fields are in the same
order, then merge operation can be used to
compute;
If they are not in the same order,
then error will occur. |
||
7 |
=join@p(A1:Employee;A2:Familymembers) |
|
||
8 |
=join(A1:Employee;A2:Familymembers) |
|
||
9 |
=join(A1:Employee1;A2:Familymembers1) |
|
||
10 |
=join(A7:Employee2;A8:Familymembers2) |
|
||
11 |
=join@x(A7:Employee2;A8:Familymembers2) |
Field values of A7 and A8 are records, the joining values will be unfolded |
Related functions: