join()
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 join field/expression xj is equal to x1, and generates a table sequence whose fields are Fi,…. which are assigned with corresponding records of the original record sequences Ai. If xj is omitted, then use the key of Ai. An omitted xj won’t be matched.
Parameters:
|
Fi |
Field name of the resulting table sequence |
|
Ai |
Sequences or record sequences to be joined |
|
xj |
Join 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 |
|
@i |
Used only to filter A1 and ignore parameter Fi; do not work with @f@1 options |
|
@d |
Used only to filter A1 to retain records that cannot be found and ignore parameter Fi; do not work with @f@1 options |
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) |
A normal join that discards non-matching items; 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) |
A full join that use nulls to represent non-matching records |
|
5 |
=join@1(A1:Employee,EID;A2:Familymembers,EID) |
A left join that uses the first table sequence as the basis and that uses nulls to correspond when no matching records can be found |
|
6 |
=join@m(A1:Employee,EID;A2:Familymembers,EID) |
If all the join 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
|
|
13 |
=join@i(A1;A2) |
Filter A1 to retain records that can be found |
|
14 |
=join@d(A1;A2) |
Filter A1 to retain records that cannot be found |
Related functions: