Depending on the type of join selected, the Data Integration Service either adds the row to the result set or discards the row. The Joiner transformation produces result sets based on the join type, condition, and input data sources.

Before you define a join condition, verify that the master and detail sources are configured for optimal performance. During a mapping run, the Data Integration Service compares each row of the master source against the detail source. To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.

Use one or more ports from the input sources of a Joiner transformation in the join condition. Additional ports increase the time necessary to join two sources. The order of the ports in the condition can impact the performance of the Joiner transformation. If you use multiple ports in the join condition, the Data Integration Service compares the ports in the order you specify.

If you join Char and Varchar datatypes, the Data Integration Service counts any spaces that pad Char values as part of the string:

The Char value is “abcd" padded with 36 blank spaces, and the Data Integration Service does not join the two fields because the Char field contains trailing spaces.

You can define a simple or advanced condition type. You can also define an expression parameter. An expression parameter is a parameter that contains the join expression. You can change the parameter value at run time with a mapping parameter.