MERGEJOIN(setofdatasets, joincondition, SORTED( fields) [, jointype] [, DEDUP ] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )
| setofdatasets | The SET of recordsets to process ([idx1,idx2,idx3]), typically INDEXes, which all must have the same format. |
| joincondition | An expression specifying how to match records in the setofdatasets. |
| 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. |
| jointype | Optional. An inner join if omitted, else one of the listed types below. |
| DEDUP | Optional. Specifies the output result set contains only unique records. |
| UNORDERED | Optional. Specifies the output record order is not significant. |
| ORDERED | Specifies the significance of the output record order. |
| bool | When False, specifies the output record order is not significant. When True, specifies the default output record order. |
| STABLE | Optional. Specifies the input record order is significant. |
| UNSTABLE | Optional. Specifies the input record order is not significant. |
| PARALLEL | Optional. Try to evaluate this activity in parallel. |
| numthreads | Optional. Try to evaluate this activity using numthreads threads. |
| ALGORITHM | Optional. Override the algorithm used for this activity. |
| name | The algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options. |
The MERGEJOIN function is a variation of the SET OF DATASETs forms of the MERGE and JOIN functions. Like MERGE, it merges records from the setofdatasets into a single result set, but like JOIN, it uses the joincondition and jointype to determine which records to include in the result set. It does not, however, use a TRANSFORM function to produce the result; it includes all records, unchanged, from the setofdatasets that match the joincondition.
The record matching joincondition may contain two parts: a STEPPED condition that may optionally be ANDed with non-STEPPED conditions. The STEPPED expression contains equality expressions of the fields from the SORTED option, ANDed together, using LEFT and RIGHT as dataset qualifiers. If not present, the STEPPED condition is deduced from the fields specified by the SORTED option.
The order of the datasets within the setofdatasets can be significant to the way the joincondition is evaluated. The joincondition is duplicated between adjacent pairs of datasets, which means that this joincondition:
LEFT.field = RIGHT.field
when applied against a setofdatasets containing three datasets, is logically equivalent to:
ds1.field = ds2.field AND ds2.field = ds3.field