Talend Joins or tJoin field helps you to perform joins or join two sources based on the key attribute and returns the output. Generally, Talend Joins performs Left Join (default) and Inner Join. However, by interchanging the two tables, we can also perform the Right join.
- Inner Join: Returns only matching records from both tables.
- Left Join: Return all the records from the left table or main table and match records from the right table or lookup table. Left Join is the default Talend Join.
- Right Join: Return all the records from the right table and matching records from the left table.
Talend Joins Example
In this example, we use employee and department data to demonstrate Talend joins. The following screenshot shows the data inside the employee’s text file and the data inside the Department text file.
First, drag and drop the DBConnection to establish a connection to SQL where we save the Talend tJoin output. Next, tDBCommit to commit the change and close the connection.
As you can see from the below screenshot, we have already created metadata for both of the above text files. So, we drag and drop both metadata into the Job design and select the tFileInputDelimited as the component.
Next, drag and drop the Talend tJoin field. As you can see from the tJoin tooltip, it performs an inner or outer join between the main data flow and a lookup flow.
Please connect the Main row from EmployeeData to tJoin. Next, connect the DepartmentName with tJoin. You can notice that it is showing row2(Lookup) for the Department name file. Talend considers the first connected one as the main table, and the second will be the lookup table. You can change the order by connecting the other one as the first table to tJoin.
The Talend Joins Component tab has three options.
- Include lookup columns in output: Please check mark this option to include the extra columns from the lookup table. For example, adding department names from the lookup table in the output.
- Key definition: Please select the Input key column and the lookup key columns. Here, you have to select the common column or primary key from the Main and Foreign Key from the lookup table.
- Inner Join (with reject output): By default, Talend tJoin performs Left Outer Join. Please check mark this option to perform Inner Join.
Next, click the Edit Schema button to add the required columns from the Main table and lookup table to the tJoin output.
Here, we are adding all the columns from EmployeeData except DeptID.
Next, we added the Department Name from the Lookup or Department table.
Please use the Plus button to add the Key definition. Let me select DeptID because it is the common column from both the text files.
You can see the Input Key and Lookup key attributes.
Until now, we haven’t included the lookup columns. So, please checkmark the Include lookup columns in the output option. It will open a new section to select the Output Column and Lookup column.
- Output Column: Please select the column name from the tJoin output.
- Lookup column: Please choose the column name from the Lookup table or Department table.
Please select the Department Name for both of them.
Here, we are saving the Talend tJoin output in an SQL Database. So, use the DBOutput, which is using the existing connection. Next, we are creating a new table Talend_Joins (if not exist) as the destination table.
Let us run the Talend Joins job.
Let me open the Management studio to check the Talend Left Outer Join result.
Talend Inner Join
Please select the Talend tJoin field, and select Inner Join rejects row to show the rejected rows in tLogRow.
Next, please checkmark the Inner Join (with reject output) option.
Once you run the Talend Joins Job, you can see the four inner join rejected rows.
Within the SQL query, you can see the Talend Inner Join output.
Talend Right Join
Technically, there is no Right join in Talend. However, by changing the Lookup and Main tables, we can accomplish the goal. So, let me set the DepartmentName table as main by right-clicking on row2(lookup) and selecting the Set this connection as the Main option.
Next, you have to add the fields from the Lookup table (I mean Employee) in the column mapping.
Let me run this Talend right join job.
The result of the Talend Right Join is