join()

Fi

Field name of the resulting table sequence

Ai

Sequences or record sequences to be joined

xj

Join field/ expression

@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 A_i are ordered against x_j, then use merge operation to compute

@p

Perform a join according to positions while ignoring parameter xj

@i

Used only to filter A₁ and ignore parameter F_i; do not work with @f@1 options

@d

Used only to filter A₁ to retain records that cannot be found and ignore parameter F_i; do not work with @f@1 options

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 uses 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.