The following joinflags options may be specified to determine exactly how the JOIN executes.

[, PARTITION LEFT | PARTITION RIGHT | [MANY] LOOKUP [ FEW] ] | GROUPED | ALL | NOSORT [ ( which ) ] | KEYED [ (index) [, UNORDERED ] ] | LOCAL | HASH ]]
[, KEEP(n) ] [, ATMOST( [ condition, ] n ) ] [, LIMIT( value [, SKIP | transform | FAIL ]) ] [, SKEW(limit [, target] ) [, THRESHOLD( size ) ] ] [, SMART ] [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ]

The following options are mutually exclusive and may only be used to the exclusion of the others in this list: PARTITION LEFT | PARTITION RIGHT | [MANY] LOOKUP | GROUPED | ALL | NOSORT | HASH

In addition to this list, the KEYED and LOCAL options are also mutually exclusive with the options listed above, but not to each other. When both KEYED and LOCAL options are specified, only the INDEX part(s) on each node are accessed by that node.

Typically, the leftrecset should be larger than the rightrecset to prevent skewing problems (because PARTITION LEFT is the default behavior). If the LOOKUP or ALL options are specified, the rightrecset must be small enough to be loaded into memory on every node, and the operation is then implicitly LOCAL. The ALL option is impractical if the rightrecset is larger than a few thousand records (due to the number of comparisons required). The size of the rightrecset is irrelevant in the case of "half-keyed" and "full-keyed" JOINs (see the Keyed Join discussion below).

Use SMART when the right side dataset is likely to be small enough to fit in memory, but is not guaranteed to fit.

If you get an error similar to this:

"error: 1301: Pool memory exhausted:..."

this means the rightrecset is too large and a LOOKUP JOIN should not be used. A SMART JOIN may be a good option in this case.

Example:

//this example demos the implicit DEDUP of the lookup dataset
r1 := {INTEGER1 fred};
r2 := {INTEGER1 fred,INTEGER1 sue};
ds1 := DATASET([1,2,3,4],r1);
ds2 := DATASET([{1,2},{2,2},
                {1,1},{2,1}, 
                {3,1},{4,1}],r2);
r2 XF(ds1 L, ds2 R) := TRANSFORM
  SELF.fred := L.fred;
  SELF.sue := R.sue;
END;
JOIN(ds1,ds2,
     LEFT.Fred = RIGHT.fred,
     XF(LEFT,RIGHT),LOOKUP);