Matches records from two data sources to create an enriched data set with values from both inputs whenever the specified field is found to exist in both sources.
In total, there are six variations of Join node that have been superseded by the new Join node. You can now perform all join variations within a single node, see Join.
This node performs a relational join on the data from both input pins, using the criteria defined in LeftInputKey and RightInputKey to determine matches, left orphans, and right orphans. The inputs must be sorted by their respective keys before entering this node.
Matches are filtered at two different phases in this node. First, only matches whose type match the JoinType are evaluated against the Script in the Script property. In other words, if the JoinType is set to "ir" for a right inner join, then any left orphans are discarded before reaching the Script property. All matches whose type matches the JoinType are then evaluated against the Script in the Script property. In this property, you can define additional filtering criteria, if desired, in a where clause on the emit statement.
In addition to the standard Script functions and variables, the Script property has four additional Script variables available to it. These variables classify the records ready to be output as either left orphans, right orphans, or matches.
- join.type - This variable will be either "l", "i", or "r", depending upon whether a left orphan, right orphan, or match was found. If a left orphan was found, then this variable is set to "l". Conversely, for a right orphan, this variable is set to "r". Finally, for a match, this variable is set to "i". Note that this variable provides more detail than the JoinType property. JoinType simply defines all relationships that will be evaluated against the Script property. join.type specifies exactly which relationship was discovered for the given record(s).
- join.rightOrphan - Set to true when a right orphan is found. This is only true when join.type is "r".
- join.leftOrphan - Set to true when a left orphan is found. This is only true when join.type is "l".
- join.match - Set to true when a match is found. This is only true when join.type is "i".
Specify the type of join. Options are l (left), i (inner), r (right), or a combination of these. For example, you could do a left inner join via "li".
Optionally specify a Script expression to be performed on each row of the first, or left, pin that determines the key on which to join to the right pin.
Optionally specify a Script expression to be performed on each row of the second, or right, pin that determines the key on which to join to the left pin.
Specify a Script that defines what to output under what conditions. The Script in this property is evaluated against the last two rows used in producing either a match or an orphan. In the case of an orphan, then the fields on the side that does not match are all NULL.
Usually, this property simply contains an emit statement that lists the fields you would like to output. However, this property could also contain a where clause that filters the outputs based on an additional set of criteria. Also, additional calculations can be made in this property that are included in the output. In sum, anything that can be done in a Filter can also be done in this Script property.
A value is required for this property.
Deprecated. Optionally specify an expression controlling what to compare.
Optionally specify whether this node will verify that the inputs are sorted according to the input key expressions.
Note that enabling this property can cause a slight performance decrease in certain circumstances. Specifically, if one input is significantly longer than the other, and no more matches can be made, then the node still has to process the rest of this longer table in order to verify that it is in order.
The default value is True.
Inputs and outputs
Inputs: left, right.
Outputs: out1, multiple optional.