join()
Here’s how to use join() function.
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:
A .join()
Description:
A foreign-key-style join between table sequences/record sequences, or a table sequence and a record sequence.
Syntax:
A.join(C:.,T:K,x:F,…; …;…)
Note:
The function matches C field of table sequence/record sequence A with the key of table sequence/record sequence T to find the desired records. Add an F field represented by x, which is T’s field expression, to A to generate a new table sequence. K can be omitted or represented by #. When omitted, K is T’s key; when written as #, K is represented by a sequence number. If there is an F field in A, just modify the existing field of A. Use the latest time calculated through now() when time key value is not specified.
Options:
|
@i |
Delete a record with a non-matching foreign key value; by default, a non-matching record will be represented by null. If parameters x:F are omitted, perform the filtering purely over parameter A |
|
@o(F;) |
Use the record as the value of F field to generate a new record; here expression x is equivalent to ~ |
|
@d |
If parameter x:F is absent, delete the records matching the foreign key and perform the filtering operation only over table sequence/record sequence A |
|
@k |
When parameter x is ~, record the relationship between parameter F and parameter C to identify the potentially associative foreign key |
|
@m |
Enable a merge join when A is ordered by C and T is ordered by K |
Parameters:
|
A |
Table sequence/record sequence |
|
C |
A’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
Table sequence/record sequence/memory table |
|
K |
T’s key |
|
x |
T’s field expression, which can be represented by ~ and #; the pound sign # represents the sequence number of a record in T; record the sequence number as 0 if a record doesn’t exist in T |
|
F |
Field name in expression x |
Return value:
A table sequence/record sequence
Example:
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE order by EID" ) |
|
|
2 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
|
|
3 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").cursor().memory().keys(EMPLOYEEID) |
Return a memory table |
|
4 |
=A1.join(EID,A2,BONUS+1:SALARY1) |
Use null to represent a record with non-matching foreign key value |
|
5 |
=A1.join(EID,A3,BONUS+1:SALARY1) |
A3 is a memory table; the resut is the same as above, where null is used to represent a record with non-matching foreign key value |
|
6 |
=A1.join@i(EID,A2,BONUS+1:SALARY1) |
Delete a record with non-matching foreign key value |
|
7 |
=A1.join@i(EID,A2:#1,BONUS+1:SALARY1) |
Same result as A4’s; #1 means the first field |
|
8 |
=A1.join@o(F1;EID,A2,BONUS+1:SALARY1) |
Records are the values of F1 field |
|
9 |
=A1.join@i(EID,A2) |
As parameters x:F are omitted, perform the filtering purely over parameter A1 |
|
10 |
=A1.join@d(EID,A2) |
Since parameters x:F are absent, delete the records matching the foreign key and perform the filtering operation only over A1
|
|
11 |
=A1.join(EID,A2,BONUS+1:SALARY) |
Modify the existing field since SALARY already exists in A1
|
ch .join()
Description:
A foreign-key-style join between a channel and a record sequence.
Syntax:
ch.join(C:.,T:K,x:F,…; …;…)
Note:
The function uses the key of table sequence/record sequence T to match C,… field in channel ch, and add an F field to ch by joining T’s field expression x to for a new channel. K can be omitted or represented by #; when it is omitted, use T’s key; when represented by #, the key is pointed by a sequence number. This is an attached computation.
Options:
|
@i |
Delete the whole record if the foreign key can’t be matched; by default make it null. |
|
@o(F;…) |
Generate a new record by introducing the original record as a new field; field expression x can be represented by ~. |
Parameters:
|
ch |
Channel |
|
C |
Foreign key of a given channel; use comma to separate a composite key |
|
T |
Table sequence/record sequence |
|
K |
Key of the given table sequence/record sequence |
|
x |
A field expression of the given table sequence/record sequence |
|
F |
Name of the field expression |
Return value:
Channel
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,SALARY from EMPLOYEE order by EID" ) |
|
|
2 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
|
|
3 |
=channel() |
Create a channel |
|
4 |
=channel() |
|
|
5 |
=channel() |
|
|
6 |
=channel() |
|
|
7 |
=A1.push(A3,A4,A5,A6) |
Be ready to push data in A1’s cursor into channel A3,A4,A5 and A6, but the action needs to wait |
|
8 |
=A3.join(EID,A2, BONUS+1:SALARY1) |
A normal join, where a record can’t be matched with the foreign key is recorded as null |
|
9 |
=A8.fetch() |
Fetch and store the existing data in the channel |
|
10 |
=A4.join@i(EID,A2: #1, #3+1:SALARY1) |
#1 represents the first field, and #3 represents the third field |
|
11 |
=A10.fetch() |
|
|
12 |
=A5.join@i(EID,A2, BONUS+1:SALARY1) |
Delete the whole record since it doesn’t match the foreign key |
|
13 |
=A12.fetch() |
|
|
14 |
=A6.join@o(F1;EID,A2,~.BONUS+1:SALARY1) |
Make the original record as the F1 field |
|
15 |
=A14.fetch() |
|
|
16 |
=A1.fetch() |
|
|
17 |
=A3.result() |
|
|
18 |
=A4.result() |
|
|
19 |
=A5.result() |
Get the same result as A18 |
|
20 |
=A6.result() |
|
cs .join()
Description:
A foreign-key-style join between a cursor and a table sequence/record sequence.
Syntax:
cs.join(C:.,T:K,x:F,…; …;…)
Note:
The function matches foreign key field C of cursor cs with the key of parameter T to find the corresponding records in T. Add an F field represented by x, which is T’s field expression, to cs and return the original cursor. K can be omitted or represented by #. When omitted, K is T’s key; when written as #, K is represented by a sequence number. It supports the multicursor.
Parameters:
|
cs |
A cursor/A multicursor |
|
C |
Cursor cs’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
A table sequence/A record sequence |
|
K |
T’s key |
|
x |
T’s field expression |
|
Field name in expression x. |
Options:
|
@i |
Delete a record with a non-matching foreign key value; by default a non-matching record will be represented by null |
|
@o(F;…) |
Use the record as the value of F field to generate a new record; here expression x can be represented by ~, which is T’s record |
|
@d |
If parameters x:F are absent, only perform a filtering over the cursor by deleting its records where the foreign key is matched |
|
@m |
Enable a merge join when A is ordered by C and T is ordered by K |
Return value:
The orignal cursor
Example:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Return retrieved data as a cursor:
|
|
2 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Return a table sequence:
|
|
3 |
=A1.join(EID,A2,BONUS+1:SALARY1) |
Normal join; a non-matching record will be displayed as null |
|
4 |
=A3.fetch() |
|
|
5 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Same as A1 |
|
6 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Same as A2 |
|
7 |
=A5.join@i(EID,A6, BONUS+1:SALARY1) |
Delete non-matching records |
|
8 |
=A7.fetch() |
|
|
9 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Same as A1 |
|
10 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Same as A2 |
|
11 |
=A9.join@i(EID,A10: #1, #3+1:SALARY1) |
#1 represents the 1st field; #3 represents the 3rd field |
|
12 |
=A11.fetch() |
Same as A8 |
|
13 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Same as A1 |
|
14 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Same as A2 |
|
15 |
=A13.join@o(F1;EID,A14,BONUS+1:SALARY1) |
Use records as values of F1field |
|
16 |
=A15.fetch() |
|
|
17 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
Same as A2 |
|
18 |
=demo.query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Same as A2 |
|
19 |
=A17.join@o(F1;EID,A18,~:SALARY1) |
Replace parameter x with the sign ~ |
|
20 |
=A19.fetch() |
|
|
21 |
=file("D:\\test7.ctx") |
|
|
22 |
=A21.create(#EMPLOYEEID,EVALUATION,BONUS;EMPLOYEEID |
Create a composite table |
|
23 |
=A22.attach(table3,#EMPLOYEEID,EVALUATION,BONUS) |
Add an attached table |
|
24 |
=demo.cursor("select * from PERFORMANCE") |
Return a table sequence |
|
25 |
=A23.append(A24) |
Append records to A23’s attached table |
|
26 |
=demo.cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
|
Related functions:
cs.join ()
Description:
A foreign-key-style join between a cluster cursor and a record sequence.
Syntax:
cs.join(C:.,T:K,x:F,…; …;…)
Note:
The function matches foreign key field C,… of cluster cursor cs with the key of parameter T to find corresponding records in table T. Add an F field represented by x, which is T’s field expression, to cs and return the original cluster cursor. K can be omitted or represented by #. When omitted, K is T’s key; when written as #, K is represented by a sequence number. It supports the multicursor.
Options:
|
@c |
With a distributed cluster table, the operation won’t involve a cross-node reference but it assumes that the referenced records are local |
Parameters:
|
cs |
A cursor/multicursor/cluster cursor |
|
C |
cs’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
A cluster memory table |
|
K |
T’s key |
|
x |
T’s field expression |
|
F |
Name of T’s field expression |
Return value:
The original cursor
Example:
|
|
A |
|
|
1 |
[192.168.18.143:8281] |
|
|
2 |
=file("emp_1.ctx":[2], A1) |
|
|
3 |
=A2.open() |
|
|
4 |
=A3.cursor() |
A cluster cursor |
|
5 |
[192.168.0.110:8281] |
|
|
6 |
=file("PERFORMANCE.ctx":[1],A5) |
|
|
7 |
=A6. open () |
|
|
8 |
=A7.cursor() |
|
|
9 |
=A8.memory() |
A cluster memory table |
|
10 |
=A4.join(EID,A9:EMPLOYEEID, BONUS*12:total) |
Match EID field of A4’s cluser cursor with the key field EMPLOYEEID of A9’s cluster memory table, make the value of calculating expression BONUS*12 over the memory table total field and join it up with the cluster cursor, and return the cluster cursor |
|
11 |
=A10.fetch() |
Fetch data from A10’s cursor
|
T.join()
Description:
A foreign-key-style join beween an in-memory table and a table sequence or two in-memory tables.
T.join(C:., Ai:K,x:F,…; …;…)
Note:
The function finds eligible records from table sequence or in-memory table Ai by matching C,… field of in-memory table T with the latter’s key, adds Ai’s expression x to T as a field, and renames it F. Represent parameter K with # or just omit it. Use Ai’s key when K is omitted, and use ordinals when it is represented by #.
Options:
|
@i |
Delete records of T that cannot match the foreign key, and by default, will record corresponding values as nulls. When parameters x:F are absent, just perform the fileting on Ai |
|
@o(F;…) |
Use the original records of Ai as a new field F to generate new records; here x is equivalent to ~ |
|
@d |
Delete records of T that match the foreign key when parameters x:F are absent, which is equivalent to performing fileting on Ai |
|
@k |
When x is represented by ~, record the correspondence relationship between F and C,…in the result set to identify the foreign key for a pre-join |
Parameters:
|
T |
An in-memory table |
|
C |
T’s foreign key; use colon to separate multiple fields of a composite foreign key |
|
Ai |
A table sequence or an in-memory table |
|
K |
Ai’s key |
|
x |
Expression of an Ai’s field |
|
F |
Field name of expression x |
Return value:
A table sequence
Example:
|
|
A |
|
|
1 |
=connect("demo").cursor("select EID,NAME,DEPT,SALARY from EMPLOYEE where EID<10 order by EID" ) |
|
|
2 |
=A1.memory() |
Return an in-memory table
|
|
3 |
=connect("demo").query("select * from PERFORMANCE order by EMPLOYEEID").keys(EMPLOYEEID) |
Return a table sequence whose key is EMPLOYEEID
|
|
4 |
=A2.join(EID,A3, BONUS*2:ALL_SALARY) |
Perform a foreign-key-style join between an in-memory table and a table sequence, during which values that cannot match the foreign key are recorded as nulls
|
|
5 |
=A2.join@i(EID,A3, #3:ALL_SALARY) |
Use @i option to delete A2’s records that cannot match the foreign key; #3 is the third field
|
|
6 |
=A2.join@i(EID,A3) |
Use @i option to delete A2’s records that cannot match the foreign key; perform filtering only on A3’s table sequence since parameters x:F are absent
|
|
7 |
=A2.join@d(EID,A3) |
Use @d option to delete A2’s records that match the foreign key; perform filtering only on A3’s table sequence since parameters x:F are absent
|
|
8 |
=A2.join@o(emp;EID,A3, BONUS*2:ALL_SALARY) |
Use @o option to make the orginal records of A2’s in-memory table the new field emp and generate new records
|
|
9 |
=A2.join@k(EID,A3, ~:ALL_SALARY) |
With @k oiption, record the correspondence relationship between ALL_SALARY and EID in the result table sequence to identify the foreign key of a pre-join when parameter x is ~
|
|
10 |
=connect("demo").cursor("select * from PERFORMANCE order by EMPLOYEEID").memory().keys(EMPLOYEEID) |
Return an in-memory table using EMPLOYOEEID as the key
|
|
11 |
=A2.join(EID,A10, BONUS*2:ALL_SALARY) |
Perform a foreign-key-style join between A2’s in-memory table and A10’s
|
