Source Qualifier – User Defined Join – Right Outer Join

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.

Source Qualifier - User Defined Join - Right Outer Join
JOB1 – This is our First table (Left)
Source Qualifier - User Defined Join - Right Outer Join
JOB2 – This is our second table (Right). We are going to join these two tables and load to target using SQ.
Source Qualifier - User Defined Join - Normal Join (Inner)
Created a mapping with Source Qualifier Transformation, We need only 1 SQ instance here with the ports from both the tables since we are writing a single query to read from the source.
Source Qualifier - User Defined Join - Right Outer Join
Edit the SQ property and give the below SQL expression in ‘User Defined Join’ editor. Just note the Join syntax, enclose the entire join statement in braces or else will get DB error.

Informatica Join Syntax

{emp_job1 RIGHT OUTER join emp_job2
on emp_job1.job_id=emp_job2.job_id}

Source Qualifier - User Defined Join - Right Outer Join
Created a workflow & session for the mapping and ran the job, Let’s review the session log. Only 5 rows got read from the source and loaded to target, All rows from the Right table (Table 2), with the matching rows in the left table. 
Source Qualifier - User Defined Join - Right Outer Join
See the Source Qualifier SQL query used in the mapping, since we used the ‘User Defined Join’ property, Integration service override the default SQL query and changed to a join query.
Source Qualifier - User Defined Join - Right Outer Join
Output Target Table – All records from Right table (Matched-3 + Unmatched-2) got loaded to target. Job ID, Title & Min Salary from first table and Max Salary from second table. Since there is no match job_id 4 and 5 showing as NULL.

Right Outer Join using Source Qualifier – ‘SQL Query’

The same result can be achieved by overriding the default SQL query. 

Right Outer Join using Source Qualifier - 'SQL Query'
Edit the SQ property and give the complete SQL query to read from the source. Integration service will use this query instead of default to get the data.

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

Right Outer Join using Source Qualifier - 'SQL Query'
Same result as above, Only 5 records loaded to target (All records from Right Table)
Right Outer Join using Source Qualifier - 'SQL Query'
See the source Qualifier SQL query in the session log. Integration uses the same query which we given in the SQL editor.
Right Outer Join using Source Qualifier - 'SQL Query'
Output Target Table – We have only those same 5 records, All records from Right table (Matched-3 + Unmatched-2)

We are done with Right Outer Join – Lets see Full Outer join in the next session.