Join Types
In a Joiner transformation, the join can originate from different types of sources.
The Joiner transformation supports the following types of join:
- •Normal
- •Master outer
- •Detail outer
- •Full outer
Note: A normal or master outer join performs faster than a full outer or detail outer join.
If a result set includes fields that do not contain data in either of the sources, the Joiner transformation populates the empty fields with null values. If you know that a field returns NULL and you do not want to insert NULLs in the target, you can set a default value for the corresponding port.
Normal Join
With a normal join, the Data Integration Service discards all rows of data from the master and detail source that do not match, based on the condition.
For example, you have two sources of data for auto parts called PARTS_SIZE and PARTS_COLOR.
The PARTS_SIZE data source is the master source and contains the following data:
PART_ID1 | DESCRIPTION | SIZE |
|---|
1 | Seat Cover | Large |
2 | Ash Tray | Small |
3 | Floor Mat | Medium |
The PARTS_COLOR data source is the detail source and contains the following data:
PART_ID2 | DESCRIPTION | COLOR |
|---|
1 | Seat Cover | Blue |
3 | Floor Mat | Black |
4 | Fuzzy Dice | Yellow |
To join the two tables by matching the PART_IDs in both sources, you set the condition as follows:
PART_ID1 = PART_ID2
When you join these tables with a normal join, the result set includes the following data:
PART_ID | DESCRIPTION | SIZE | COLOR |
|---|
1 | Seat Cover | Large | Blue |
3 | Floor Mat | Medium | Black |
The following example shows the equivalent SQL statement:
SELECT * FROM PARTS_SIZE, PARTS_COLOR WHERE PARTS_SIZE.PART_ID1 = PARTS_COLOR.PART_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.
When you join the sample tables with a master outer join and the same condition, the result set includes the following data:
PART_ID | DESCRIPTION | SIZE | COLOR |
|---|
1 | Seat Cover | Large | Blue |
3 | Floor Mat | Medium | Black |
4 | Fuzzy Dice | NULL | Yellow |
Because no size is specified for the Fuzzy Dice, the Data Integration Service populates the field with a NULL.
The following example shows the equivalent SQL statement:
SELECT * FROM PARTS_SIZE RIGHT OUTER JOIN PARTS_COLOR ON (PARTS_COLOR.PART_ID2 = PARTS_SIZE.PART_ID1)
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.
When you join the sample tables with a detail outer join and the same condition, the result set includes the following data:
PART_ID | DESCRIPTION | SIZE | COLOR |
|---|
1 | Seat Cover | Large | Blue |
2 | Ash Tray | Small | NULL |
3 | Floor Mat | Medium | Black |
Because no color is specified for Ash Tray, the Data Integration Service populates the field with NULL.
The following example shows the equivalent SQL statement:
SELECT * FROM PARTS_SIZE LEFT OUTER JOIN PARTS_COLOR ON (PARTS_SIZE.PART_ID1 = PARTS_COLOR.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 the following data:
PARTED | DESCRIPTION | SIZE | Color |
|---|
1 | Seat Cover | Large | Blue |
2 | Ash Tray | Small | NULL |
3 | Floor Mat | Medium | Black |
4 | Fuzzy Dice | NULL | Yellow |
Because no color is specified for the Ash Tray and no size is specified for the Fuzzy Dice, the Data Integration Service populates the fields with NULL.
The following example shows the equivalent SQL statement:
SELECT * FROM PARTS_SIZE FULL OUTER JOIN PARTS_COLOR ON (PARTS_SIZE.PART_ID1 = PARTS_COLOR.PART_ID2)