JOIN(leftrecset, rightrecset, joincondition [, transform] [, jointype] [, joinflags] )
JOIN(setofdatasets, joincondition, transform, SORTED( fields) [, jointype] )
| leftrecset | The left set of records to process. |
| rightrecset | The right set of records to process. This may be an INDEX. |
| joincondition | An expression specifying how to match records in the leftrecset and rightrecset or setofdatasets (see Matching Logic discussions below). In the expression, the keyword LEFT is the dataset qualifier for fields in the leftrecset and the keyword RIGHT is the dataset qualifier for fields in the rightrecset. |
| transform | Optional. The TRANSFORM function to call for each pair of records to process. If omitted, JOIN returns all fields from both the leftrecset and rightrecset, with the second of any duplicate named fields removed. |
| jointype | Optional. An inner join if omitted, else one of the listed types in the JOIN Types section below. |
| joinflags | Optional. Any option (see the JOIN Options section below) to specify exactly how the JOIN operation executes. |
| setofdatasets | The SET of recordsets to process ([idx1,idx2,idx3]), typically INDEXes, which all must have the same format. |
| SORTED | Specifies the sort order of records in the input setofdatasets and also the output sort order of the result set. |
| fields | A comma-delimited list of fields in the setofdatasets, which must be a subset of the input sort order. These fields must all be used in the joincondition as they define the order in which the fields are STEPPED. |
| Return: | JOIN returns a record set. |
The JOIN function produces a result set based on the intersection of two or more datasets or indexes (as determined by the joincondition).
JOIN(leftrecset, rightrecset, joincondition [, transform] [, jointype] [, joinflags] )
The first form of JOIN processes through all pairs of records in the leftrecset and rightrecset and evaluates the condition to find matching records. If the condition and jointype specify the pair of records qualifies to be processed, the transform function executes, generating the result.
JOIN dynamically sorts/distributes the leftrecset and rightrecset as needed to perform its operation based on the condition specified, therefore the output record set is not guaranteed to be in the same order as the input record sets. If JOIN does do a dynamic sort of its input record sets, that new sort order cannot be relied upon to exist past the execution of the JOIN. This principle also applies to any GROUPing--the records are automatically "un-grouped" as needed except under the following circumstances:
* For LOOKUP and ALL joins, the GROUPing and sort order of the leftrecset are preserved.
* For KEYED joins the GROUPing (but not the sort order) of the leftrecset is preserved.