Joiner Transformation in Informatica

Joiner Transformation in Informatica PowerCenter

Joiner Transformation in Informatica is an active, connected transformation that combines related rows from two input pipelines. It is particularly useful when the data comes from heterogeneous sources, such as an Oracle table and a DB2 table, a database table and a flat file, or two flat files.

A single Joiner accepts two input pipelines, called the master and detail pipelines. It compares their join-key ports and produces rows according to the configured join type. To combine more than two pipelines, use additional Joiner transformations or stage the data where the required join can be performed safely.

Homogeneous and Heterogeneous Joins in Informatica

Homogeneous join: The participating tables are accessible through the same relational source and database connection. When the database can perform the join efficiently, joining in the Source Qualifier or source SQL usually reduces the number of rows transferred to the Integration Service.

Example: The EMP and DEPT tables are in the same Oracle database and can be joined by DEPTNO.

Heterogeneous join: The inputs come from sources that cannot participate in one source-database query. Joiner Transformation combines the pipelines after the Integration Service reads them.

Examples: An Oracle table joined with a DB2 table, a database table joined with a flat file, or two flat files joined by a shared business key.

Source-database joins are not automatically better in every situation. Choose between source SQL, Joiner Transformation, Lookup Transformation, and staging by considering data volume, source capabilities, indexes, network transfer, cache requirements, maintainability, and measured session performance.

Joiner Transformation Join Types

Joiner Transformation supports four join types. The meaning of master outer and detail outer is based on which pipeline’s unmatched rows are retained.

Join type	Rows retained	SQL comparison
Normal	Only rows whose join keys match in both pipelines	Inner join
Master outer	All detail rows and matching master rows	Outer join that preserves the detail side
Detail outer	All master rows and matching detail rows	Outer join that preserves the master side
Full outer	Matching and unmatched rows from both pipelines	Full outer join

Normal Join in Joiner Transformation

A normal join discards unmatched rows from both the master and detail pipelines. It returns rows only when the configured join keys match.

Master Outer Join in Joiner Transformation

A master outer join retains every detail row and the matching master rows. Unmatched master rows are discarded. This naming can appear counterintuitive, so verify which pipeline must be preserved before selecting the join type.

Detail Outer Join in Joiner Transformation

A detail outer join retains every master row and the matching detail rows. Unmatched detail rows are discarded.

Full Outer Join in Joiner Transformation

A full outer join retains matching rows and unmatched rows from both pipelines. Ports belonging to the missing side of an unmatched row are returned as null.

Null handling: Null join keys do not match one another in the normal equality-based join condition. If the business rule requires null values to match, normalize them to a carefully selected replacement value before the Joiner. Make sure that value cannot collide with valid source data.

Master and Detail Pipelines in Joiner Transformation

The Joiner caches rows while combining the two pipelines. Master and detail selection therefore affects memory, cache files, and runtime. For an unsorted Joiner, a common starting point is to use the smaller input as the master because the master rows are cached. For sorted input, data distribution and duplicate join-key values also matter.

Estimate rows and row width rather than considering only the number of records.
Filter unused records before the Joiner when the business rule permits.
Pass only the ports needed by the join and downstream logic.
Review the session log for cache sizes, cache-file activity, throughput, and bottlenecks.
Measure changes with representative data instead of assuming that one master-detail arrangement is always faster.

Sorted Input for Informatica Joiner Transformation

The sorted-input option can reduce cache requirements when both pipelines arrive in the required order. Enable it only when each input is sorted by the join ports and the port order matches the join-condition order. Incorrect ordering can produce incorrect results rather than only slower execution.

Sort both pipelines by the join keys in the same ascending order.
Place join-key ports in the same order in the Joiner condition.
Preserve the required order through upstream transformations.
Confirm that the cost of sorting does not exceed the cache or runtime benefit.
Validate row counts and matched keys after enabling sorted input.

Joiner vs Lookup vs Union Transformation

Transformation	Primary purpose	Typical use
Joiner	Combine columns from two related pipelines	Join heterogeneous sources using equality-based keys
Lookup	Retrieve related reference values	Enrich a pipeline, test whether a key exists, or obtain a dimension key
Union	Append compatible rows vertically	Combine pipelines with corresponding port structures into one stream

Joiner and Lookup both combine related data, but their data flow differs. Joiner receives two pipelines and can return ports from both sides. A connected Lookup enriches an incoming pipeline from a lookup source, while an unconnected Lookup is called when a return value is needed. Union does not match keys or add columns; it appends rows with compatible structures.

Create a Joiner Transformation in Informatica PowerCenter

In this Informatica tutorial, EMP and DEPT can be used to illustrate a normal join on DEPTNO. Create or import the JOINER_EMP target in Informatica PowerCenter Designer before building the mapping.

Open PowerCenter Designer and connect to the required repository folder.
Import EMP and DEPT as source definitions.
Create or import JOINER_EMP as the target definition.
Create a mapping and add EMP, DEPT, and JOINER_EMP.
Keep separate source pipelines when demonstrating an actual Joiner Transformation.
Add a Joiner Transformation to the mapping.
Connect the required EMP ports to one Joiner input group and DEPT ports to the other.
Select the master pipeline in the Ports tab after reviewing the expected data volume.
Create the equality condition between the EMP and DEPT department-number ports.
Select Normal, Master Outer, Detail Outer, or Full Outer according to the required output.
Connect the required Joiner output ports to JOINER_EMP.
Validate the mapping before creating the session and workflow.

Create the JOINER_EMP Target Definition

Open Informatica PowerCenter Designer and connect to the appropriate repository folder.
Go to Tools | Target Designer | Create.
Enter a name for the target definition and select its database type.

Open the target definition and add the required columns.
The illustrated target includes EmpNo, Dname, Job, Sal, and DeptNo. Match each datatype, precision, scale, and nullability setting to the target database design.

Save the target definition after reviewing its columns.
If the physical target does not exist, use the generated SQL as a starting point and review it before execution.
Use a database account intended for development or deployment. Do not place reusable credentials in tutorial text, mappings, parameter files committed to source control, or screenshots.

Review the generated DDL, confirm the schema and database connection, and execute it only in the intended environment.

Create the EMP and DEPT Mapping

Go to Mappings | Create and create a mapping. The screenshots use the name m_SQ_HOMOGENEOUS_JOIN.

Add the JOINER_EMP target definition to the mapping.

Source Qualifier Join Shown in the EMP and DEPT Screenshots

The screenshots below demonstrate a homogeneous Source Qualifier join, not a separate Joiner Transformation object. EMP and DEPT are from the same database, so their ports are combined in one Source Qualifier and the database performs the join. This is different from the two-pipeline Joiner procedure described above.

Add EMP, DEPT, and JOINER_EMP to the mapping. When both relational tables use the same compatible source connection, connect their required ports through one Source Qualifier and allow the generated or overridden SQL to perform the join.

Open the Source Qualifier and rename it to SQ_EMPDEPT if that name makes the combined source clearer.
Add the required DeptNo, Dname, and LOC ports from DEPT to the combined Source Qualifier.
Connect the required Source Qualifier ports to JOINER_EMP.

Open SQ_EMPDEPT, select the Properties tab, and open the SQL Query editor to inspect or override the query.

Select the configured development data source and authenticate through the approved connection method. Use environment-specific connection objects or parameterized configuration instead of embedding credentials in mapping logic.

Generate the SQL and inspect the selected columns, table aliases, join predicate, and schema qualification. Confirm that the join condition relates EMP and DEPT through the intended department key. Do not accept generated SQL without reviewing its semantics.

Save the query, validate the mapping, and resolve datatype or connectivity errors before creating the session.

Create a Session and Workflow for the Join Mapping

The remaining process is to create Session, create Workflow, and run the workflow. Configure the correct source and target connections, target load behavior, error handling, and session properties before execution.

In Workflow Manager, open the workflow and start it after verifying its session configuration.

Open Workflow Monitor and check the workflow and session status. If the session fails, inspect the first meaningful error in the session log, along with reader, transformation, and writer statistics.

Validate the JOINER_EMP Output

Query the JOINER_EMP target and compare its contents with the expected join result. Do not rely only on the workflow’s success status. Validate source counts, matched keys, unmatched-row behavior, target counts, rejected rows, duplicate keys, and representative values.

Joiner Transformation Limitations and Design Considerations

A single Joiner combines two input pipelines. More inputs require staged logic or additional transformations.
Join conditions are based on equality between master and detail ports. Apply other filtering before or after the join according to the intended semantics.
Join-key datatypes should be compatible. Convert values explicitly when sources represent the same key differently.
Null join keys do not match automatically.
Duplicate keys can produce multiple output combinations. Profile key cardinality before interpreting an unexpectedly high row count as an error.
Outer joins produce nulls for ports from the missing side. Downstream expressions and target constraints must handle those nulls.
Sorted input requires correctly ordered data on both pipelines. An incorrect assumption about sorting can affect results.
Available options and restrictions can differ between PowerCenter and Informatica cloud services. Apply documentation for the product and release being used.

Joiner Transformation Performance Tuning

Join in the source database when both inputs are available there and measurement confirms that the database plan is suitable.
Filter rows and remove unused ports before the Joiner.
Select the master pipeline using actual row counts, row widths, duplicates, and cache behavior.
Use sorted input only after validating sort order and measuring the full cost of sorting.
Review indexes and source predicates when the alternative is a source-database join.
Provide enough cache and temporary disk space for representative production volume.
Inspect session-log statistics instead of relying on general claims that one join type or transformation is always faster.
Validate performance changes without changing the required join semantics.

Joiner Transformation FAQs

What is the difference between Lookup and Joiner Transformation?

Joiner combines two input pipelines and can return ports from both. Lookup retrieves related data from a lookup source for an incoming row. Lookup is often used for reference-data enrichment or existence checks, while Joiner is appropriate when two pipelines must be combined as peer inputs.

What is the difference between Joiner and Union Transformation?

Joiner combines columns horizontally by matching keys. Union appends rows vertically from compatible input groups. Union does not compare business keys or add columns from a related row.

How can one Joiner combine more than two sources?

It cannot combine more than two input pipelines directly. Join the first two pipelines, then send that result and the third pipeline into another Joiner. For complex multi-source logic, staging the data and joining it in a database may be easier to validate and tune.

Which input should be master in Joiner Transformation?

For an unsorted Joiner, the smaller input is a reasonable starting choice for the master because the master side is cached. Row width, duplicate keys, sorted-input behavior, memory, and runtime distribution also matter, so confirm the choice with session-log measurements.

Does Joiner Transformation match null keys?

No. Two null join keys are not treated as equal. If the business rule requires them to match, replace nulls consistently before the Joiner using a collision-safe value and document that rule.

Joiner Transformation QA Checklist

Confirm whether the mapping uses an actual Joiner Transformation or a Source Qualifier database join.
Verify the master and detail pipelines against expected row volume and cache behavior.
Check join-key datatype, precision, scale, case, whitespace, and null handling.
Confirm that the selected join type preserves the correct unmatched side.
Profile duplicate keys and predict their effect on output row counts.
For sorted input, verify the sort keys, order, and preservation of sorting on both pipelines.
Validate matched rows, unmatched rows, duplicates, rejects, and target totals after execution.
Review session logs for cache usage, throughput, warnings, and rejected records.
Ensure that connection credentials are managed through approved environment configuration.