Join Types

The Join command has six join types, based on key field comparisons. The output option you choose determines which records are included in the output table.

The types of Joins are detailed in the table below

Join types can be chosen in the Join command dialog from either the Join type pull-down menu or by clicking on the adjacent Join type Venn Diagram.

Matched Primary Records Only

Creates an output table of selected fields from the primary and secondary tables for primary table records with key field matches in the secondary table.

If there is more than one key field match in the secondary table, Analyzer uses the first matched record it finds. If no matching record is found, no output record is produced.

Note: This is the default option.

All Primary Records

Creates an output table of selected fields from the primary and secondary tables for all records in the primary table, including those with no matches in the secondary table. The fields for those records with no secondary table match are filled with blanks or nulls, depending on the field type.

Non-matching records from the secondary table are ignored.

Matched Primary and All Secondary Records

Creates an output table of selected fields from the primary and secondary tables for all records from the secondary table, including those with no match in the primary table. Fields for those records with no match in the primary table are filled with blanks or nulls, depending on the field type.

Non-matching records from the primary table are ignored.

All Primary and Secondary Records

Creates an output table of selected fields for all records from both primary and secondary tables, whether the records have matches or not. The fields for unmatched records from either table and duplicates from the secondary table are filled with blanks or nulls, depending on the field type.

Unmatched Primary Records Only

Creates an output table of all records from the primary table that had no matches in the secondary table. The output table includes primary fields only because there is no secondary information if records have no match.

Matched Many-to-Many Records

Creates an output table of selected fields from the primary and secondary tables for primary table records with key field matches in the secondary table.

Tip: It is strongly recommended that you apply a filter to a matched many-to-many Join to manage the size of the resulting table.

Note: When filtering either of the many-to-many Joins, Analyzer allows secondary fields to be referenced in the filter.

This option differs from the Matched Primary Records option—if there is more than one key field match in the secondary table, Analyzer uses all matched records it finds. If no matching record is found, no output record is produced.

Note: When filtering either of the many-to-many Joins, Analyzer allows secondary fields to be referenced in the filter.

Users with an Arbutus zSeries Server connection to an IMS database can also perform joins that include an IMS dataset as the secondary table. Select this option to perform a one-to-many type of join with an IMS dataset as the secondary table.

Note: Analyzer supplies an error message to the Command Log if the IMS secondary table is not HDAM.

Example

This example illustrates an keyed matched many-to-many Join on two monthly files containing a customer number key field (NO) and a monthly balance (BALANCE):

The result of a matched many-to-many Join would be:

Many-to-Many Records

This Join replicates a true SQL Join. It is un-keyed and matches every record in the primary table with every record in the secondary table.

Tip: It is strongly recommended that you apply a filter to a many-to-many Join to manage the size of the resulting table. Consider that un-unfiltered many-to-many Join of two tables (one with 7,000 records and one with 5,000 records) will create an exponentially larger table (7,000 X 5,000 = 35,000,000 records in the output table).

This option differs from the Matched Many-To-Many option which is a keyed Join matching every key in the primary table with every matching key in the secondary table.

Note: When filtering either of the many-to-many Joins, Analyzer allows secondary fields to be referenced in the filter.

Note: When filtering either of the many-to-many Joins, Analyzer allows secondary fields to be referenced in the filter.

Example

This example illustrates an un-keyed many-to-many Join on two monthly files containing a customer number (NO) and a monthly balance (BALANCE):

The result of an unkeyed many-to-many Join would be: