Joiner transformation is used to join two heterogeneous sources. You can join data from the same source type as well. The minimum criteria to join the data are matching columns in both the sources. A mapping indicating the Joiner transformation is shown in the following screenshot:
A Joiner transformation has two pipelines; one is called master and the other is called detail. One source is called the master source and the other is called detail. We do not have left or right joins like we have in the SQL database.
To use a Joiner transformation, drag all the required columns from two sources into the Joiner transformation and define the join condition and join type in the properties.
As mentioned in the preceding section, one source is called master and the other is called detail. By default, when you add the first source, it becomes the detail and the other becomes the master. You can decide to change the master or detail source. To make a source the master, check the Master port for the corresponding source, as shown in the following screenshot:
Always verify that the master and detail sources are defined to enhance the performance. It is always recommended that you create a table with a smaller number of records as the master and the other as the detail. This is because Integration Service picks up the data from the master source and scans the corresponding record in the details table. So if we have a smaller number of records in the master table, fewer iterations of scanning will happen. This enhances the performance.
Join conditions are the most important condition to join the data. To define the join condition, you need to have a common port in both the sources. Also, make sure the data type and precision of the data you are joining is same. You can join the data based on multiple columns as well. Joining the data on multiple columns increases the processing time. Usually, you join the data based on key columns such as the primary key/foreign key of both the tables.
Joiner transformations do not consider NULL as matching data. If it receives NULL in the data, it does not consider them to be matching.
To define a join condition, double-click on the Joiner transformation, click on the Condition tab, and define the new condition.
You can define multiple conditions to join two tables.
The Joiner transformation matches the data based on the join type defined. Similar to SQL, Joiner transformation use the join type to join the data. Let's discuss the join type in detail by taking the following example. We have two sources, the master source as EMPLOYEE_TABLE and the detail source as EMPLOYEE_FILE:
EMPLOYEE_TABLE (Master Source – Oracle`) EMPLOYEE_ID,AGE 101,20 102,30 103,20 EMPLOYEE_FILE (Detail Source – Flat File) EMPLOYEE_ID,SAL 101,1000 103,4000 105,2000 106,4000 110,5000
As you can see, we have created a table with fewer records as the master source to enhance performance. To assign the join type, double-click on the Joiner transformation and click on the Properties tab. Select the join type out of the four types from the drop-down list, as shown in the following screenshot:
When you define a normal join, Integration Service allows only matching records from both the master and detail source and discards all other records.
For the preceding scenario, we will set the join condition as EMPLOYEE_ID = EMPLOYEE_ID.
The result of the normal join for the previously-mentioned data is as follows:
EMPLOYEE_ID,AGE,SAL 101,20,1000 103,20,4000
All nonmatching records with a normal join will get rejected.
When you define a full join, Integration Service allows all the matching and nonmatching records from both the master and detail source.
The result of the full join for the previously mentioned data is as follows:
EMPLOYEE_ID,AGE,SAL 101,20,1000 102,30,NULL 103,20,4000 105,NULL,2000 106,NULL,4000 110,NULL,5000
When you define a master outer join, Integration Service allows all matching records from both the master and detail source and also allows all other records from the details table.
The result of the master outer join for the previously mentioned data is as follows:
EMPLOYEE_ID,AGE,SAL 101,20,1000 103,20,4000 105,NULL,2000 106,NULL,4000 110,NULL,5000
We you define a detail outer join, Integration Service allows all matching records from both the master and detail source and also allows all other records from the master table.
The result of the detail outer join for the previously mentioned data is as follows:
EMPLOYEE_ID,AGE,SAL 101,20,1000 102,30,NULL 103,20,4000
With this, we have learned about the various options available in Joiner transformations.