Skip to main content

MERGEJOIN

MERGEJOIN(setofdatasets, joincondition, SORTED( fields) [, jointype] [, DEDUP ] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

setofdatasetsThe SET of recordsets to process ([idx1,idx2,idx3]), typically INDEXes, which all must have the same format.
joinconditionAn expression specifying how to match records in the setofdatasets.
SORTEDSpecifies the sort order of records in the input setofdatasets and also the output sort order of the result set.
fieldsA 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.
jointypeOptional. An inner join if omitted, else one of the listed types below.
DEDUPOptional. Specifies the output result set contains only unique records.
UNORDEREDOptional. Specifies the output record order is not significant.
ORDEREDSpecifies the significance of the output record order.
boolWhen False, specifies the output record order is not significant. When True, specifies the default output record order.
STABLEOptional. Specifies the input record order is significant.
UNSTABLEOptional. Specifies the input record order is not significant.
PARALLELOptional. Try to evaluate this activity in parallel.
numthreadsOptional. Try to evaluate this activity using numthreads threads.
ALGORITHMOptional. Override the algorithm used for this activity.
nameThe 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.

Matching Logic

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

Join Types:

The following jointypes produce the following types of results, based on the records matching produced by the joincondition:

INNEROnly those records that exist in all datasets in the setofdatasets.
LEFT OUTERAt least one record for every record in the first dataset in the setofdatasets.
LEFT ONLYOne record for every record in the first dataset in the setofdatasets for which there is no match in any of the subsequent datasets.
MOFN(min [,max])One record for every record with matching records in min number of adjacent datasets within the setofdatasets. If max is specified, the record is not included if max number of dataset matches are exceeded.

Example:

Rec := RECORD,MAXLENGTH(4096)
  STRING1 Letter;
  UNSIGNED1 DS;
END;
ds1 := DATASET([{'A',1},{'B',1},{'C',1},{'D',1},{'E',1}],Rec);
ds2 := DATASET([{'A',2},{'B',2},{'H',2},{'I',2},{'J',2}],Rec);
ds3 := DATASET([{'B',3},{'C',3},{'M',3},{'N',3},{'O',3}],Rec);
ds4 := DATASET([{'A',4},{'B',4},{'R',4},{'S',4},{'T',4}],Rec);
ds5 := DATASET([{'B',5},{'V',5},{'W',5},{'X',5},{'Y',5}],Rec);
SetDS := [ds1,ds2,ds3,ds4,ds5];
j1 := MERGEJOIN(SetDS,
                STEPPED(LEFT.Letter=RIGHT.Letter),
                SORTED(Letter));
j2 := MERGEJOIN(SetDS,
                STEPPED(LEFT.Letter=RIGHT.Letter),
                SORTED(Letter),LEFT OUTER);
j3 := MERGEJOIN(SetDS,
                STEPPED(LEFT.Letter=RIGHT.Letter),
                SORTED(Letter),LEFT ONLY);
j4 := MERGEJOIN(SetDS,
                STEPPED(LEFT.Letter=RIGHT.Letter),
                SORTED(Letter),MOFN(3));
j5 := MERGEJOIN(SetDS,
                STEPPED(LEFT.Letter=RIGHT.Letter),
                SORTED(Letter),MOFN(3,4));
OUTPUT(j1);
OUTPUT(j2);
OUTPUT(j3);
OUTPUT(j4);
OUTPUT(j5);

See Also: MERGE, JOIN, STEPPED