Examples of join types

Examples follow that illustrate the six types of joins:

Examples of the first five join types use the common key field of employee number (Empno) to join the primary Payroll Ledger table and the secondary Employee Records table, shown below. The first five join types are many-to-one joins.



The Payroll Ledger table has a single pay period and includes all payroll disbursements. One employee, 003, received two cheques.

The Employee Records table is maintained by the human resources department. Employee records consist of a complete list of valid employees and the amount that they are paid each period. One employee, 002, is missing from the table.

In each example, the objective is to test for employees who have been paid incorrectly.

Matched primary records

This type of join creates one output record for every record in the primary table that has a match in the secondary table.



The results of this join show all employees who have been paid and who are also listed in the employee records table. Analysis of the output table allows you to determine if any valid employees were paid incorrectly. Note how the two employee 003 records in the primary table (‘many’) are joined to the single employee 003 record in the secondary table (‘one’).

Unmatched primary records

This type of join creates one output record for every record in the primary table that does not have a match in the secondary table.



The results of this join show all employees who have been paid but who are not listed in the employee records table. Analysis of the output table allows you to identify checks issued to invalid employee numbers. The output table does not include any fields from the secondary table because by definition this type of join includes only those primary records that do not have any matching secondary table information.

If you use this type of join with a primary transaction table and a secondary master table, the output identifies and isolates transactions for which no matching master information exists. If you invert the relation of the tables, and join a primary master table with a secondary transaction table, the output identifies and isolates master records without any matching transactions.

Matched and unmatched primary records

This type of join creates one output record for every record in the primary table that has a match in the secondary table, and one additional record for every unmatched record in the primary table.



The results of this join are equivalent to the combined results of the two preceding tests. You can account for all checks issued. ACL fills missing secondary fields for unmatched primary records with blanks or zeros.

Matched primary records, and unmatched secondary records

This type of join creates one output record for every record in the primary table that has a match in the secondary table, and one additional record for every unmatched record in the secondary table.



The results of this join let you account for all employees listed in the employee records table. You can identify valid employees who may have been paid incorrectly and those who were not paid at all. ACL fills missing primary fields for unmatched secondary records with blanks or zeros.

Matched primary records, and unmatched primary and secondary records

This type of join creates one output record for every record in the primary table that has a match in the secondary table, and one additional record for every unmatched record in the primary and secondary tables.



The results of this join account for all payroll checks issued and all employees in the employee records table. You can identify incorrect payments (amounts and possible duplicates), payments to invalid employee numbers, and employees who were not paid at all. ACL fills missing fields for unmatched records with blanks or zeros.

Many-to-many matched records

This type of join creates one output record for every match between records in the primary and secondary tables, including matches between primary key values and duplicate secondary key values.

The many-to-many example below uses the common key field of employee number (Empno) to join the primary Payroll Ledger table and the secondary Employee Records table.



The complete Payroll Ledger table has all pay periods and all payroll disbursements for 2011. The example uses January and February disbursements for employee 006.

The Employee Records table is maintained by the human resources department. Employee records consist of a complete list of valid employees, the amount that they are paid each period, and their start date with the company, and in any new position within the company. The example focuses on employee 006, who was promoted and given a pay raise on 01 February 2011.

As shown below, the many-to-many join matches primary key values to every occurrence of a matching secondary key value. By contrast, the many-to-one join matches primary key values to the first occurrence only of a matching secondary key value. If additional secondary key matches exist, they are ignored.





The two different methods of joining tables yield different results. The many-to-many join correctly indicates that employee 006 was the recipient of a raise beginning in February. The many-to-one join incorrectly indicates that the employee was twice overpaid by $200 dollars in February. If both source tables in a join contain legitimate multiple occurrences of matching key values, you need to use the many-to-many join to ensure you are capturing all relevant data and deriving accurate results.

Note

You may need to include a filter with a many-to-many join – that is, create a conditional join – to remove superfluous joined records. The following filter ensures that only valid Pay Date-Start Date combinations are used in producing the many-to-many output table below.

IF Pay_date <= `20110131` AND Secondary_Table.Start_date = `20080915` OR Pay_date > `20110131` AND Secondary_Table.Start_date = `20110201`



Note

Use the many-to-many join cautiously because it is capable of producing a table that is much larger than the combined size of the two original tables.

Related concepts
About joining tables
Types of joins
Automatic harmonization when joining tables
Related tasks
Joining tables


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback