join()
Here’s how to use join() functions.
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.
Parameter:
|
Fi |
Field name of the resulting table sequence |
|
Ai |
Sequences or record sequences to be joined |
|
xj |
Join field/ expression |
Option:
|
@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 |
|
@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(A8:Employee2;A9:Familymembers2) |
|
|
11 |
=join@i(A1;A2) |
Filter A1 to retain records that can be found |
|
12 |
=join@d(A1;A2) |
Filter A1 to retain records that cannot be found |
Related function:
P .join()
Description:
A foreign-key-style join between table sequences/record sequences, or a table sequence and a record sequence.
Syntax:
P.join(C:.,T:K,x:F,…; …;…)
Note:
The function matches C field of table sequence/record sequence P 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 P to generate a new table sequence.
K can be omitted or represented by #. When omitted, K is T’s key by default; when written as #, K is the ordinal number of a record of table T, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and increase performance.
If there is an F field in P, just modify the existing field of P. Use the latest time calculated through now() when time key value is not specified.
Option:
|
@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 P |
|
@o(F;) |
Use the record as the value of F field to generate a new record; here expression x is equivalent to ~ |
|
@d |
If parameters x:F are absent, delete records matching the foreign key and perform the filtering operation only over table sequence/record sequence P |
|
@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 P is ordered by C and T is ordered by K |
Parameter:
|
P |
Table sequence/record sequence |
|
C |
P’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
Table sequence/record sequence |
|
K |
T’s key |
|
x |
T’s field expression, which can be represented by ~ and #; the pound sign # represents the ordinal number of a record in T; record the ordinal number as null if a record doesn’t exist in T |
|
F |
Field name in expression x |
Return value:
A table sequence/record sequence
Example:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT * FROM CITIES") |
Data of CITIES table:
|
|
2 |
=connect("demo").query("SELECT * FROM STATECAPITAL where STATEID<30").keys(STATEID) |
Data of STATECAPITAL table:
|
|
3 |
=A1.join(STATEID,A2,CAPITAL) |
Associate CITIES table and STATECAPITAL table through the foreign key, during which default parameter K is STATEID, the key of STATECAPTITAL, and add STATECAPITAL’s CAPITAL field to CITIES to generate a new table sequence
|
|
4 |
=A1.join(STATEID,A2:#,CAPITAL) |
As STATEID field values are natural numbers starting from 1, which correspond to ordinal numbers of records in STATECPATITAL table, parameter K is written as # to use those ordinal numbers in order to increae efficiency; return same result as A3 |
|
5 |
=A1.join@i(STATEID,A2,CAPITAL) |
@i option enables deleting records with non-matching foreign key values; write them as nulls if there isn’t the option
|
|
6 |
=A1.join@i(STATEID,A2) |
@i option enables filtering CITIES table only when parameters x:F are absent
|
|
7 |
=A1.join@d(STATEID,A2) |
With @d option and when parameters x:F are absent, delete records where foreign key values are matching and perform filtering only on CITIES table
|
|
8 |
=A1.join(STATEID,A2,abc) |
Write records where values of parameter x cannot be found in A2 as nulls
|
|
9 |
=A1.join@k(STATEID,A2,~:STATES) |
With @k option and when parameter x is ~, write correspondence relationship between STATES and STATEID in the result table sequence to identify foreign key for pre-association
|
|
10 |
=A1.join@o(cities;STATEID,A2,CAPITAL) |
@o option enables to take the whole orignal record as a cities value to generate a new record; this is equivalent to an expression where parameter x is ~
|
|
11 |
=A1.join(STATEID,A2,CAPITAL:NAME) |
Modify the existing fields when NAME field already exists in CITIES table
|
Perform the JOIN through MERGE method:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT * FROM CITIES").sort(STATEID) |
Below is data of CITIES table:
|
|
2 |
=connect("demo").query("SELECT * FROM STATECAPITAL where STATEID<30").keys(STATEID).sort(STATEID) |
Below is data of STATECAPITAL table:
|
|
3 |
=A1.join@m(STATEID,A2,CAPITAL) |
As CITIES table is ordered by STATEID and STATECAPITAL table is ordered by parameter K and with @m option being present, we can use MERGE method to compute
|
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. ordinal number.
K can be omitted or represented by #. When omitted, K is T’s key by default; when written as #, K is the ordinal number of a record of table T, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and increase performance.
If there is an F field in ch, just modify the existing field of ch. Use the latest time calculated through now() when time key value is not specified.
This is an attached computation.
Option:
|
@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 written as ~, which represents the whole record of ch |
|
@d |
Delete the whole record matching foreign key value when parameters x:F are absent and perform filtering on ch only |
|
@m |
Use MERGE method during parallel computation when ch is ordered by the foreign key and T is ordered by its key |
Parameter:
|
ch |
A 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. ordinal number.
K can be omitted or represented by #. When omitted, K is T’s key by default; when written as #, K is the ordinal number of a record of table T, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and increase performance.
If there is an F field in cs, just modify the existing field of ch. Use the latest time calculated through now() when time key value is not specified.
It supports the multicursor.
Parameter:
|
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. |
Option:
|
@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 written as ~, which represents the whole record of cs |
|
@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 cs is ordered by C and T is ordered by K |
Return value:
The orignal cursor
Example:
|
|
A |
|
|
1 |
=connect("demo").cursor("SELECT * FROM CITIES") |
Below is data of CITIES table:
|
|
2 |
=connect("demo").query("SELECT * FROM STATECAPITAL where STATEID<30").keys(STATEID) |
Below is data of STATECAPITAL table:
|
|
3 |
=A1.join(STATEID,A2,CAPITAL) |
Associate CITIES table and STATECAPITAL table through the foreign key – parameter K is by default STATEID, STATECAPITAL’s key, and add STATECAPITAL table’s CAPTITAL field to result to generate a new table sequence
|
|
4 |
=A1.join(STATEID,A2:#,CAPITAL) |
STATEID field values are natural numbers starting from 1, which correspond to ordinal numbers of records of STATECAPITAL table, so we can write parameter K as # to use ordinal numbers in STATECAPITAL table so that efficiency can be boosted; return same result as A3 |
|
5 |
=A1.join@i(STATEID,A2,CAPITAL) |
Use @i option to delete records where foreign key values are non-matching; by default write the records as nulls
|
|
6 |
=A1.join@i(STATEID,A2) |
Use @i option to perform filtering only on CITIES table when parameters x:F are absent
|
|
7 |
=A1.join@d(STATEID,A2) |
Use @d option to delete records matching the foreign key when parameters x:F are absent and perform filtering only on CITIES table
|
|
8 |
=A1.join(STATEID,A2,abc) |
Write a record as null when parameter x cannot be found in A2
|
|
9 |
=A1.join@k(STATEID,A2,~:STATES) |
Use @k option to record correspondence relationship between STATES and STATEID in result table sequence to identify foreign key for pre-association when parameter x is written as ~
|
|
10 |
=A1.join@o(cities;STATEID,A2,CAPITAL) |
Use @o option to make the whole original record cities value to generate a new record; this is equivalent to writing parameter x as ~
|
|
11 |
=A1.join(STATEID,A2,CAPITAL:NAME) |
Modify the existing field as NAME already exists in CITIES table
|
Related function:
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. ordinal number.
K can be omitted or represented by #. When omitted, K is T’s key by default; when written as #, K is the ordinal number of a record of table T, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and increase performance.
If there is an F field in cs, just modify the existing field of ch. Use the latest time calculated through now() when time key value is not specified.
It supports the multicursor.
Option:
|
@c |
With a distributed cluster table, the operation won’t involve a cross-node reference but it assumes that the referenced records are local |
Parameter:
|
cs |
A cursor/multicursor/cluster cursor |
|
C |
cs’s foreign key; separate multiple fields in a composite key with the colon |
|
T |
A cluster in-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 in-memory table |
|
10 |
=A4.join(EID,A9:EMPLOYEEID, BONUS*12:total) |
Match EID field of A4’s cluster cursor with the key field EMPLOYEEID of A9’s cluster in-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 between a pseudo table and a table sequence/record sequence.
Syntax:
T.join(C:.,A:K,x:F,…; …;…)
Note:
The function matches C field of pseudo table T with the key of table sequence/record sequence A to find the desired records. Add an F field represented by x, which is A’s field expression, to T to generate a new pseudo table.
K can be omitted or represented by #. When omitted, K is A’s key by default; when written as #, K is the ordinal number of a record of table A, which means foreign key numberization. Simply put, primary key values of the dimension table are natural numbers starting from 1, which are row numbers corresponding to table records. In this case, we can directly locate dimension table records according to key values by row numbers. This helps speed up association with the dimension table and further increase performance.
If there is an F field in T, just modify the existing field of A. Use the latest time calculated through now() when time key value is not specified.
Option:
|
@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 is equivalent to ~, which is a whole record of T |
|
@d |
When parameters x:F are absent, delete whole records matching the foreign key and perform the filtering operation only over pseudo table T |
|
@m |
Enable a merge join when T is ordered by C and A is ordered by K |
Parameter:
|
T |
A pseudo table |
|
C |
T’s foreign key; separate multiple fields in a composite key with the colon |
|
A |
Table sequence/record sequence |
|
K |
A’s key |
|
x |
A’s field expression |
|
F |
Field name in expression x |
Return value:
Pseudo table
Example:
|
|
A |
|
|
1 |
=create(file).record(["cities.ctx"]) |
Below is content of composite table cities.ctx:
|
|
2 |
=pseudo(A1) |
Generate a pseudo table from the composite table |
|
3 |
=connect("demo").query("SELECT * FROM STATECAPITAL where STATEID<30").keys(STATEID) |
Below is content of STATECAPITAL:
|
|
4 |
=A2.join(STATEID,A3,CAPITAL) |
Associate A2’s pseudo table CITIES and A3’s STATECAPITAL table through the foreign key, during which default parameter K is STATEID, the key of STATECAPTITAL, and add STATECAPITAL’s CAPITAL field to CITIES to generate a new pseudo table, whose content is as follows:
|
|
5 |
=A2.join(STATEID,A3:#,CAPITAL) |
As STATEID field values are natural numbers starting from 1, which correspond to ordinarl numbers of records in STATECPATITAL table, parameter K is written as # to use those ordinal numbers in order to increae efficiency; return same result as A4 |
|
6 |
=A2.join@i(STATEID,A3,CAPITAL) |
@i option enables deleting records with non-matching foreign key values; write them as nulls if there isn’t the option
|
|
7 |
=A2.join@i(STATEID,A3) |
@i option enables filtering CITIES table only when parameters x:F are absent
|
|
8 |
=A2.join@d(STATEID,A3) |
With @d option and when parameters x:F are absent, delete records where foreign key values are matching and perform filtering only on pseduo table CITIES
|
|
9 |
=A2.join(STATEID,A3,abc) |
Write records where values of parameter x cannot be found in A2 as nulls
|
|
10 |
=A2.join@o(cities;STATEID,A3,CAPITAL) |
@o option enables to take the whole orignal record as a cities value to generate a new record; this is equivalent to an expression where parameter x is ~
|
|
11 |
=A2.join(STATEID,A3,CAPITAL:NAME) |
Modify the existing fields when NAME field already exists in CITIES table
|
