join()

Read(523) Label: join multiple sequences,

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 relational field/expression xj is equal to x1, and generates a table sequence whose fields are Fi,…. which reference the records of the original record sequences Ai. If xj is omitted, then use the key of Ai. An omitted xj won’t be matched. No matter how many record sequences are mutually related, the equivalence determination is conducted according to the x1 in A1. Therefore this is a one-to-many relationship.

Parameters:

Fi

Field name of the resulting table sequence

Ai

Sequences or record sequences to be joined

xj

Relational 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

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)

Normal join. The non-matching items will be discarded. 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)

Full join. If no matches, then use the nulls

5

=join@1(A1:Employee,EID;A2:Familymembers,EID)

Left join. Take the first table sequence as the basis, and use nulls if no matching items are found

 

6

=join@m(A1:Employee,EID;A2:Familymembers,EID)

If all the relational 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

Related functions:

xjoin()