A .join()

Description:

A foreign-key-style join between table sequences/record sequences, or a table sequence and a record sequence.

Syntax:

A.join(C:.,T:K,x:F,…; …;…)

Note:

The function matches C field of table sequence/record sequence A 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 A to generate a new table sequence. K can be omitted or represented by #. When omitted, K is T’s key; when written as #, K is represented by a sequence number.

Options:

@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 A

@o(F;)

Use the record as the value of F field to generate a new record; here expression x is equivalent to ~

@d

If parameter x:F is absent, delete the records matching the foreign key and perform the filtering operation only over table sequence/record sequence A

@k

When parameter x is ~, record the relationship between parameter F and parameter C to identify the potentially associative foreign key

Parameters:

A

Table sequence/record sequence

C

A’s foreign key; separate multiple fields in a composite key with the colon

T

Table sequence/record sequence/memory table

K

T’s key

x

T’s field expression, which can be represented by ~ and #; the pound sign # represents the sequence number of a record in T; record the sequence number as 0 if a record doesn’t exist in T

F

Field name in expression x

Return value:

A table sequence/record sequence

Example:

 

A

 

1

=demo.query("select * from EMPLOYEE order by  EID" )

 

2

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").keys(EMPLOYEEID)

 

3

=demo.query("select * from PERFORMANCE order by  EMPLOYEEID").cursor().memory().keys(EMPLOYEEID)

Return a memory table

4

=A1.join(EID,A2,BONUS+1:SALARY1)

Use null to represent a record with non-matching foreign key value

5

=A1.join(EID,A3,BONUS+1:SALARY1)

A3 is a memory table; the resut is the same as above, where null is used to represent a record with non-matching foreign key value

6

=A1.join@i(EID,A2,BONUS+1:SALARY1)

Delete a record with non-matching foreign key value

7

=A1.join@i(EID,A2:#1,BONUS+1:SALARY1)

Same result as A4’s; #1 means the first field

8

=A1.join@o(F1;EID,A2,BONUS+1:SALARY1)

Records are the values of F1 field

9

=A1.join@i(EID,A2)

As parameters x:F are omitted, perform the filtering purely over parameter A1

10

=A1.join@d(EID,A2)

Since parameter x:F is absent, delete the records matching the foreign key and perform the filtering operation only over A1