[Informatica] Joiner Types

 Joiner Types in Informatica

JOINER:
Joiner transformation can be used to join source data from two same or related heterogeneous sources having a comman joining key.port. The Joiner transformation uses a condition that matches one or more pairs of ports between the two sources. You can add more Joiner transformation to the mapping if you need to join more than two sources.
Let us first try to understand the two participating tables in a Join, namely Master and Detail. The joiner types are further classified based on this convention.

Master Table: Master table in Informatica is always cached. Therefore to improve performance, Master table should the table ehaveving lesser number of rows.
•Edit Joiner Transformation > Ports Tab > Select M for Master table.
Detail Table: Detail table contains detailed data and therefore is always the table having more number of rows.
•Edit Joiner Transformation > Ports Tab > Select D for Detail table.

The Joiner transformation supports the following types of joins:
- Normal
- Master Outer
- Detail Outer
- Full Outer

--NORMAL JOIN--

With a normal join, the PowerCenter Server discards all rows of data from the master and detail source that do not match, based on the condition.
Consider the below two tables (master and detail)

PROD_SIZE   (master source)
PROD_ID1      DESCRIPTION     SIZE
1                  ToothPaste        Large
2                  Soap                 Small
3                  Broom               Medium

PROD_COLOR (detail source)
PROD_ID2      DESCRIPTION        COLOR
1                  ToothPaste           White
3                  Soap                    Blue
4                  Bedsheet              Yellow

To join the two tables by matching the PART_IDs in both sources, you set the condition as follows:
PROD_ID1 = PROD_ID2

PROD_ID     DESCRIPTION    SIZE       COLOR
1                ToothPaste      Large     White
2                Soap               Small      Blue

SQL Equivalent
SELECT * FROM PROD_SIZE, PROD_COLOR WHERE PROD_SIZE.PROD_ID1 =
PROD_COLOR.PROD_ID2
    ************************
--MASTER OUTER JOIN--

A master outer join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
Using the above two tables in Master outer join gives the below result.

PROD_ID      DESCRIPTION     SIZE       COLOR
1                ToothPaste        Large     White
2                Soap                 Small      Blue
4                Bedsheet           NULL     Yellow

Notice that the SIZE value for Bedsheet is populated as NULL. This is because Bedsheet  does not have entry in Master table.

SQL Equivalent
SELECT * FROM PROD_SIZE LEFT OUTER JOIN PROD_COLOR ON
(PARTS_SIZE.PART_ID1 = PROD_COLOR.PART_ID2)

    ************************
--DETAIL OUTER JOIN--

A detail outer join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
Using the above two tables in Detail outer join gives the below result.

PROD_ID     DESCRIPTION      SIZE          COLOR
1                ToothPaste        Large        White
2                Soap                 Small         Blue
3                Broom               Medium     NULL

Notice that the COLOR value for Broom is populated as NULL. This is because Broom  does not have entry in Detail table.

SQL Equivalent
SELECT * FROM PROD_SIZE RIGHT OUTER JOIN PROD_COLOR ON
(PROD_COLOR.PART_ID1 = PROD_SIZE.PART_ID2)


    ************************
--FULL OUTER JOIN--

A full outer join keeps all rows of data from both the master and detail sources.
When you join the sample tables with a full outer join and the same condition, the result set includes:

PROD_ID      DESCRIPTION    SIZE         COLOR
1                ToothPaste       Large       White
2                Soap                Small        Blue
3                Broom              Medium    NULL
4                Bedsheet          NULL        Yellow

SQL Equivalent
SELECT * FROM PROD_SIZE FULL OUTER JOIN PROD_COLOR ON
(PROD_SIZE.PART_ID1 = PROD_COLOR.PART_ID2)

Notice here that both SIZE and COLOR are populated as NULL for Bedsheet and Broom respectively.