tweakstreet-logo
  • Enter search terms

Join on Condition

Joins rows based on a condition

Processing

The step accepts two streams of rows, coming from two input gates left and right. It matches left and right rows based on a condition evaluated for each pair of rows. If the condition evaluates to true, the rows form a matching pair.

Join Algorithm

The step uses a join algorithm that loops over one side of the join repeatedly, while looking at each row of the other side only once. Performance is best if the side that is looped over has fewer input rows, and the side streamed through has more.

Settings

Name Type Description
Join
Mode
 string

The join mode to use. Possible modes are:

  • INNER
    Output contains only rows for which a matching row on the other side has been found. Any rows without matches are omitted.
  • LEFT OUTER
    Output contains all left rows. If any left rows have no match on the right side, the right fields on the output row are set to nil.
  • RIGHT OUTER
    Output stream contains all right rows. If any right rows have no match on the left side, the left fields on the output row are set to nil.
  • FULL OUTER
    Output contains all left and all right rows. If any rows have no match on the other side, the other side’s fields on the output row are set to nil.

Evaluated once when step initializes
Method
 string

The processing method of the algorithm. You can optimize performance by specifying which input is expected to have more rows.

Evaluated once when step initializes
Condition
 N/A

The condition that determines whether two rows are a match.

The condition is evaluated and the result is cast to boolean. If the resulting value is true, the rows are joined in the output.

The following variables are available:

Variable Type Description
left.field Fields coming in from the left gate i.e. left.customer_name
right.field Fields coming in from the right gate i.e. right.customer_name
left_row dict The entire row coming from the left gate
right_row dict The entire row coming from the right gate

Evaluated for each pair of rows
Output
Output
 N/A

Determines the structure of output rows:

  • left fields, then right fields
    Output rows contain fields from left and right rows, in order. In case of field name conflicts right row values are preserved.
  • right fields, then left fields
    Output rows contain fields from right and left rows, in order. In case of field name conflicts left row values are preserved.
  • left fields only
    Output rows contain fields from left rows only. Fields from the right row can be placed manually in the output using results.right
  • right fields only
    Output rows contain fields from right rows only. Any fields from the left row can be placed manually in the output using results.left
  • none
    Output rows are empty. Values from left and right rows must be placed manually in the output using results.left and results.right
Left Fields Prefix
 N/A Field names from the left side are prefixed with this in the output.
Right Fields Prefix
 N/A Field names from the right side are prefixed with this in the output.

Results

Name Type Description
left
 dict Currently matched left row. This value is nil for empty joins that are the result of an outer join.
right
 dict Currently matched right row. This value is nil for empty joins that are the result of an outer join.
On this page