Configure the Join Node
A Join node represents a relational multiway join operation.
Prerequisites
You have configured the operator with the Join node and connected output of two or more previous nodes to the Join node.
Context
The Join node can perform multiple step joins on two or more inputs. Configure the Join node to define the join condition and the output columns of the Join node.
Procedure
- Double-click the Join node.
-
Create a join.
If there are more than two sources at the input of the join node, first create the join between the sources.
- In the Definition tab, select a source.
-
Choose (Create Join) and drag the cursor to
another source on the canvas with which you want to create a join.
You can also create a join by selecting a column in the source and dragging the cursor to the required column in a different source. In this case, the tool automatically creates a join condition.
-
Define the join.
The Join Definition pane, define the join type and the join condition.
-
In the Join Type dropdown list, select a
value.
Join Type
Description
Inner Join
Use when each record in the two tables has matching records.
Left Outer
Output all records in the left table, even when the join condition does not match any records in the right table.
Right Outer
Output all records in the right table, even when the join condition does not match any records in the left table.
Full Outer
Output all matching records from both tables. The records are output regardless of whether they are contained in both tables. In other words, if a record is in the left table and matches the criteria, it is output even though the same record is not in the right table.
Cross
Output all possible combinations of rows from the two tables.
- In the expression editor, enter a join condition.
-
(Optional) To use the join condition that the tool proposes, select
Propose Condition.
The tool analyzes the sources participating in the join and proposes a condition.
-
In the Join Type dropdown list, select a
value.
-
Define output columns.
In the Columns tab, define the output columns of the join node.
- In the toolbar, choose the Columns tab.
-
In the Mapping pane, select a Source column
and hold and drag the cursor to the Drop row here
zone in the Target section.
- (Optional) If you want to automap the columns based on column names, in the Source section, choose (Auto Map by Name).
-
If you want to edit a column name, select a
Target column and choose (Edit).
-
If you want to remap an output column with a different source column,
right-click the mapping and choose Remap.
Select a new source column and choose OK.
-
(Optional) Reorder output columns.
In the Columns tab toolbar, switch to the Form pane to reorder the output columns.
- To reorder the columns, select the column that you want to move and in the toolbar click the up or down arrows.
-
Connect nodes.
If you want to configure the Data Transform operator with another node,
- In the menu bar, use the breadcrumb navigation to navigate back to the operator configuration editor.
- (Optional) Add new nodes.
- To connect the nodes, select the output port of a node and drag the cursor to an input port of another node.