What are the Joins?

In SQL, a join is a relational operator that combines data from multiple tables into a single result set. The Joiner transformation acts in much the same manner, except that tables can originate from different databases or flat files.

Types of Joins:

1. Normal joins: 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.
2. Master Outer join: This 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.
3. Detail Outer join: This 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.
4. Full Outer join: A full outer join keeps all rows of data from both the master and detail sources.

Example:

In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join:

All employees of 10, 20 and 30 will be there as only they are matching.

Synax: DEPT.DEPTNO=EMP.DEPTNO

Master Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL.

Syntax: {EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Detail Outer Join:

All employees of 10, 20 and 30 will be there. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Full Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Caution: Curly braces are needed in Syntax.

Note: A normal or master outer join performs faster than a full outer or detail outer join.