From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

Doing an inner join using a tMap component


You often need to perform joins between data sources to get the desired results. tMap is a transformation component that allows you to do joins. Both inner joins and outer joins are supported. In this procedure, you will learn how to do an inner join on tMap and manage the matched and rejected data.



This procedure was written with:

  • Talend Open Studio for Data Integration 5.4.1
  • Sun JDK build 1.6.0_26-b03
  • Windows XP SP3
  • MySQL 5.0.67-community-nt

Talend verified this procedure to be compatible with all versions of Talend Studio.



Requirement description

In this example, there are two data sources: a text file named in.txt and a table named person in MySQL database. The data of each data source are read as follows:




person table

user_id name country email
1 Shong CN
4 Elisa FR


This example reads the data from the in.txt file as a main flow, and then does an inner join with the data read from the person table on a tMap component based on the id column from the file and the user_id column from the table. The join will get rows, including the external columns from lookup table, if the id already exists in the table, and will reject rows if the id does not exist in the table.


Here is the expected output:

Matched rows:

id name sex email
1 Shong boy
4 Elisa girl


Unmatched rows:

id name sex
2 Ross boy
3 Sabrina girl


Create a demo job

Create a demo Job called InnerJoinDemo. The Job design is as follows:

tFileInputDelimited_1: reads data from in.txt file.

tMysqlInputDelimited_1: reads data from person table.

tMap_1: performs an inner join and generates two output tables.

tLogRow_1 and tLogRow_2: print output data to the console.



The following images show the detailed component settings:







tMap_1: performs an inner join between the two incoming data flows, and generates two output tables: one table for the matched rows, and another for the unmatched rows.



tLogRow_1, tLogRow_2: print the output data to the console with table model.



Execute job

Execute the Job. The console shows the following results:

Starting job InnerJoinDemo at 20:56 19/12/2013.
[statistics] connecting to socket on port 3790
[statistics] connected
|          tLogRow_1           |
|id|name |sex |email           |
|1 |Shong|boy ||
|4 |Elisa|girl||
|   tLogRow_2   |
|id|name   |sex |
|2 |Ross   |boy |
|3 |Sabrina|girl|
[statistics] disconnected
Job InnerJoinDemo ended at 20:56 19/12/2013. [exit code=0]

The results show that all matched rows are output into tLogRow_1 and all unmatched rows are output into tLogRow_2.

Version history
Revision #:
6 of 6
Last update:
‎02-24-2019 11:08 PM
Updated by:
Labels (2)
Nine Stars

Yes this is very helpful while doing inserts and updates by using lookups, and this concept also useful while we apply merge concepts for synchronizing