Use the Source Qualifier and the Application Source Qualifier transformations to perform an outer join of two sources in the same database. When the Integration Service performs an outer join, it returns all rows from one source table and rows from the second source table that match the join condition.
Use an outer join when you want to join two tables and return all rows from one of the tables. For example, you might perform an outer join when you want to join a table of registered customers with a monthly purchases table to determine registered customer activity. Using an outer join, you can join the registered customer table with the monthly purchases table and return all rows in the registered customer table, including customers who did not make purchases in the last month. If you perform a normal join, the Integration Service returns only registered customers who made purchases during the month, and only purchases made by registered customers.
With an outer join, you can generate the same results as a master outer or detail outer join in the Joiner transformation. However, when you use an outer join, you reduce the number of rows in the data flow. This can improve performance.
The Integration Service supports two kinds of outer joins:
Left.
Integration Service returns all rows for the table to the left of the join syntax and the rows from both tables that meet the join condition.
Right.
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.
Use outer joins in nested query statements when you override the default query.
ASK INFAPreview
