When you join related tables in one Source Qualifier transformation, the Integration Service joins the tables based on the related keys in each table.
This default join is an inner equijoin, using the following syntax in the WHERE clause:
Source1.column_name = Source2.column_name
The columns in the default join must have:
A primary key-foreign key relationship
For example, you might see all the orders for the month, including order number, order amount, and customer name. The ORDERS table includes the order number and amount of each order, but not the customer name. To include the customer name, you need to join the ORDERS and CUSTOMERS tables. Both tables include a customer ID, so you can join the tables in one Source Qualifier transformation.
The following figure shows joining two tables with one Source Qualifier transformation:
When you include multiple tables, the Integration Service generates a SELECT statement for all columns used in the mapping. In this case, the SELECT statement looks similar to the following statement: