joinx()
Description:
Join table sequences retrieved from a series of cursors.
Syntax:
joinx(csi:Fi,xj,..;…)
Note:
The function applies MERGE algorithm over a resulting set generated from a series of ordered cursors csi and returns a new cursor. If parameter xj is omitted, join the cursors according to their primary keys; if parameter xj is present but there isn’t any primary key, perform the join based on values of xj. The function can be also used to join multiple multicursors where each one must have the same number of parallel cursors or subcursor. Parameter csi can be a table sequence.
Supposing multiple cursors csi are already ordered by join field/expression xj, the function performs a join between them on the condition that xj’s value is equivalent to the value of x1 field and returns a cursor consisting of Fi,…. fields. Fi,…. are referencing fields that reference the records of the cursors csi. Note: xj… only supports the ascending order.
Regardless of the number of cursors being joined, the equivalence determination is conducted according to cursor cs1’s x1 field. Therefore, it is a one-to-many relationship.
This is a delayed function.
Option:
|
@f |
Full join. If no matching records are found, then use nulls to correspond |
|
@1 |
Left join. Note that it is the number “1”, instead of letter “l” |
|
@p |
Perform a join according to positions, while ignoring the parameter xj |
|
@i |
Used only to filter A1 cs1 and ignore parameter Fi; do not work with @f@1 options |
|
@d |
Used only to filter cs1 to retain records that cannot be found and ignore parameter Fi; do not work with @f@1 options |
Parameter:
|
csi |
Cursors/table sequences being joined |
|
Fi |
Field name of the result table sequence |
|
xj |
Join field/expression |
Return value:
Cursor
Example:
Join by the primary key:
|
|
A |
|
|
1 |
=demo.query("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE " ).keys(EID).cursor() |
Return a cursor where the table’s primary key is EID and whose data is as follows:
|
|
2 |
=demo.query("select top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " ).keys(EID) |
Return a table sequence whose key is EID and whose data is as follows:
|
|
3 |
=joinx(A1:Emp;A2:Family) |
Join by the primary key. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Use @f option to perform full join:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" ) |
Return a cursor whose data is as follows:
|
|
2 |
=demo.query("select top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " ) |
Return a cursor whose data is as follows:
|
|
3 |
=joinx@f(A1:Emp,EID;A2:Family,EID) |
Use @f option to enable a full join that records a field as null when no matching value can be found. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Use @1 to perform left join:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" ) |
Return a cursor whose data is as follows:
|
|
2 |
=demo.query("select top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " ) |
Return a cursor whose data is as follows:
|
|
3 |
=joinx@1(A1:Emp,EID;A2:Family,EID) |
Use @1 option to enable left join based on the first cursor and that records a value that does not have a matching value in another cursor as null. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Use @p option to perform position-based join:
|
|
A |
|
|
1 |
=demo.cursor("select EID,NAME,DEPT,GENDER from EMPLOYEE where EID>2 and EID<10" ) |
Return a cursor whose data is as follows:
|
|
2 |
=demo.query("select top 10 EID,NAME,RELATION,GENDER,AGE from FAMILY " ) |
Return a cursor whose data is as follows:
|
|
3 |
=joinx@p(A1:Emp,EID;A2:Family,EID) |
Use @p option to perform position-based join. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Use @i option to enable a filtering operation only:
|
|
A |
|
|
1 |
=demo.cursor("select top 10 EID,NAME,DEPT,GENDER from EMPLOYEE" ).sortx(DEPT) |
Return a cursor whose data is as follows:
|
|
2 |
=demo.query("select top 5 * from DEPARTMENT " ).sort(DEPT) |
Return a table sequence whose data is as follows:
|
|
3 |
=joinx@i(A1,DEPT;A2,DEPT) |
Use @i option to filter cursor A1, during which records that match A2 are retained. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Use @d option to enable a filtering operation opposite to @i:
|
|
A |
|
|
1 |
|
Return a cursor whose data is as follows:
|
|
2 |
=demo.query("select top 5 * from DEPARTMENT " ).sort(DEPT) |
Return a table sequence whose data is as follows:
|
|
3 |
=joinx@d(A1,DEPT;A2,DEPT) |
Use @d option to filter cursor A1, during which records that do not match A2 are retained. |
|
4 |
=A3.fetch() |
Fetch data from cursor A3:
|
Related function: