How to join two tables – Source Qualifier – User Defined Join – Right Outer
** Source Qualifier Transformation is the most efficient way to join two tables in the same Database **
There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can create a join override to do a Normal, Left & Outer join using ‘User Defined join’ but will get a database error if you try to do Full outer join. Integration service does not support Full outer join. For that we can override the SQL query and get it done.
User-Defined Join Create a join override. The Integration Service appends the join override to the WHERE or FROM clause of the default query.
SQL Query Override the Default query with Join SQL query.
Right Outer Join
Integration Service returns all rows for the table to the right of the join syntax and the rows from both tables that meet the join condition. In other words it returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
Informatica Join Syntax
{emp_job1 RIGHT OUTER join emp_job2 on emp_job1.job_id=emp_job2.job_id}
Right Outer Join using Source Qualifier – ‘SQL Query’
The same result can be achieved by overriding the default SQL query.
Right Outer Join Query
SELECT EMP_JOB1.JOB_ID, EMP_JOB1.JOB_TITLE, EMP_JOB1.MIN_SALARY, EMP_JOB2.MAX_SALARY FROM emp_job1 RIGHT OUTER join emp_job2 on emp_job1.job_id=emp_job2.job_id
We are done with Right Outer Join – Lets see Full Outer join in the next session.