How to join two tables – Source Qualifier – User Defined Join
** 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.
Normal Join
The integration service process only matched records from both the tables and discard those that do not match.
We can also give the SQL expression like below, which would be same as INNER JOIN and give you the same results. emp_job1.job_id = emp_job2.job_id.
We are done with Normal Join – Lets see the other option by overriding source qualifier SQL Query.
How to join two tables – Source Qualifier – SQL Query
We are done with Normal Join – Lets see Left Outer join in the next session.