Talend Joins

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 the 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 employs and department data to demonstrate Talend joins. The following screenshot shows the data inside the employee’s text file.

Left Table

And the data inside the Department text file is

Right Table 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.

Talend Joins 3

As you can see from the below screenshot, we have already created metadata for both the above text file. So, we drag and drop both metadata into Job design and select the tFileInputDelimited as the component.

Talend Joins 4

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.

Talend Joins 5

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. Because Talend considers 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.

Talend Joins 6

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.

Talend Joins 7

Here, we are adding all the columns from EmployeeData except DeptID.

Talend Joins 8

Next, we added the Department Name from the Lookup or Department table.

Talend Joins 9

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.

Talend Joins 10

You can see the Input Key and Lookup key attributes.

Talend Joins 11

Until now, we haven’t included the lookup columns. So, please checkmark the Include lookup columns in output option. It will open a new section to select the Output Column and Lookup column.

Talend Joins 12
  • 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.

Talend Joins 13

Here, we are saving the Talend tJoin output in a SQL Database. So, use the DBOutput, which is using the existing connection. Next, we are creating a new table Talend_Joins (if not exists) as the destination table.

Talend Joins 15

Let us run the Talend Joins job.

Run the Job

Let me open the Management studio to check the Talend Left Outer Join result.

Left Join Output

Talend Inner Join

Please select the Talend tJoin field, and select Inner Join rejects row to show the rejected rows in tLogRow.

Right Click on the Row to Change Order

Next, please checkmark the Inner Join (with reject output) option.

Talend Joins 19

Once you run the Talend Joins Job, you can see the four inner join rejected rows.

Talend Joins 20

Within the SQL query, you can see the Talend Inner Join output.

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 the row2(lookup) and select the Set this connection as the Main option.

Talend Joins 22

Next, you have to add the fields from the Lookup table (I mean Employee) in the columns mapping.

Talend Joins 23

Let me run this Talend right join job.

Talend Joins 24

And the result of the Talend Right Join is

Right Join Output