A.pjoin ()
Description:
Create association between multiple table sequences/record sequences through the join key.
Syntax:
A.pjoin(K:..,x:F,…;Ai:z,Ki:…,xi:Fi,…; …)
Note:
The function associates table sequence/record sequence A and table sequence/record sequence Ai through the join key and returns a sequence consisting of x:F,… and xi:Fi,…; by default, x:F,… involves all fields of P.
When relationship between P and Ai is one-to-many, xi is an aggregate expression.
When relationship between P and Ai is many-to-one, records of Ai will appear repeatedly in the result set.
Parameter z specifies the join type. It can be absent or null. Perform an inner join when z is absent, and a left join when z is null; and only retain records of P that cannot find matches when z is null and both parameters xi:Fi are absent.
Parameter:
|
P |
A table sequence/record sequence |
|
K |
A’s join key |
|
x |
A’s expression |
|
F |
Field name corresponding to expression x |
|
Ai |
A table sequence/record sequence |
|
z |
Join type |
|
Ki |
Join key of Ai |
|
xi |
Expression of Ai |
|
Fi |
Field name corresponding to expression xi |
Return value:
Table sequence
Option:
|
@o |
Work when data is ordered to enable MERGE |
|
@f |
Enable full join while ignoring parameter z |
Example:
Multi-homo-dimension table join:
|
|
A |
|
|
1 |
=connect("demo").query("select STATEID,NAME from STATENAME") |
Return a table sequence:
|
|
2 |
=connect("demo").query("select STATEID,POPULATION from STATEINFO") |
Return a table sequence:
|
|
3 |
=connect("demo").query("select STATEID,CAPITAL from STATECAPITAL") |
Return a table sequence:
|
|
4 |
=A1.pjoin(STATEID,STATEID:ID,NAME;A2,STATEID,POPULATION;A3,STATEID,CAPITAL) |
Associate A1, A2 and A3 through join key STATEID and rename STATEID ID; below is the result:
|
When A and Ai has a one-to- many relationship:
|
|
A |
|
|
1 |
=demo.query("select top 6 DEPT,MANAGER from DEPARTMENT") |
Below is content of the returned table sequence:
|
|
2 |
=demo.query("select EID,NAME,DEPT from EMPLOYEE") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(DEPT;A2,DEPT,count(EID):Num) |
A1 and A2 have a one-to-many relationship; associate them through DEPT, find the number of EID values under each DEPT value, and make the computing results a new field Num; below is the returned data:
|
When A and Ai has a many-to-one relationship:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
A1 and A2 have a many-to-one relationship; associate them through STATEID, during which A2’s field values appear in the result set repeatedly; below is the result:
|
When using @o option:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Return a sequence ordered by STATEID; below is the content:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Return a sequence ordered by STATEID; below is the content:
|
|
3 |
=A1.pjoin@o(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
As sequences are ordered by the join key, use @o option to perform MERGE.
|
Use @f option to enable full join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES") |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL") |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin@f(STATEID,CID,NAME,POPULATION;A2,STATEID,CAPITAL ) |
Use @f option to perform full join, during which field values are displayed as null for non-matching records; below is the result:
|
Method 1 for left join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID,CID,NAME,POPULATION;A2:null,STATEID,CAPITAL ) |
As parameter z is null, perform left join to list all records of A1 display field values of A2’s non-matching records as nulls; below is the result:
|
Method 2 for left join:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT top 20 CID,NAME,POPULATION,STATEID FROM CITIES").sort(STATEID) |
Below is content of the returned table sequence:
|
|
2 |
=connect("demo").query("SELECT top 20 STATEID,CAPITAL FROM STATECAPITAL").sort(STATEID) |
Below is content of the returned table sequence:
|
|
3 |
=A1.pjoin(STATEID;A2:null,STATEID) |
Parameter z is null and parameters xi:Fi are absent, so only retain A1’s non-matching records; below is the result:
|
