How the JOIN Command Works

Reference: Increasing Retrieval Speed in Joined Data Sources

The JOIN command enables you to report from two or more related data sources with a single request. Joined data sources remain physically separate, but are treated as one. Up to 1022 joins can be in effect at any one time.

When two data sources are joined, one is called the host file; the other is called the cross-referenced file. Each time a record is retrieved from the host file, the corresponding fields in the cross-referenced file are identified if they are referenced in the report request. The records in the cross-referenced file containing the corresponding values are then retrieved.

Two data sources can be joined using a conditional join whenever you can define an expression that determines how to relate records in the host file to records in the cross-referenced file. Two data sources can be joined using an equijoin when they have fields in each data source with formats (character, numeric, or date) and values in common. The common formats ensure proper interpretation of the values. For example, suppose that you need to read data from two data sources: one named JOB, containing job information, and a second named SALARY, containing salary information. You can join these two data sources if each has a field identifying the same group of employees in the same way: by last name, serial number, or social security number. The join becomes effective when common values (for example, common social security numbers) are retrieved for the joined fields.

After you issue the JOIN command, you can issue a single TABLE, TABLEF, MATCH FILE, or GRAPH request to read the joined data source. You only need to specify the first data source (host file) to produce a report from two or more data sources. For example, assume you are writing a report from the JOB and SALARY data sources. You execute the following equijoin:

JOIN EMP_ID IN JOB TO ALL EMP_ID IN SALARY

This command joins the field EMP_ID in the JOB file to the field EMP_ID in the SALARY file. JOB is the host file and SALARY is the cross-referenced file. You then execute this report request:

TABLE FILE JOB
PRINT SALARY AND JOB_TITLE BY EMP_ID
END

The first record retrieved is a JOB file record for employee #071382660. Next, all records in the SALARY data source containing employee #071382660 are retrieved. This process continues until all the records have been read.

You can base your join on:

• Real fields that have been declared in the Master Files of the host and cross-referenced data sources, respectively. See Join Real Fields.
• A virtual field in the host data source (that has either been defined in the Master File or with a DEFINE command) and a real field that has been declared in the Master File of the cross-referenced data source. See Join From a Virtual Field to a Real Field.
• A condition you specify in the JOIN command itself. See Create a Conditional JOIN.