Joining versus relating

Joining and relating are both operations that allow you to combine data from tables with different record structures. However, one process might be better suited to a particular task than the other. For example, joining might be a better choice for investigative work because it outputs a permanently joined new third table. Relating might be a better choice for informational work because it creates a virtual table without any requirement that it be made permanent. Table 1 compares the two processes.

If the tables you want to combine have identical record structures, you need to use extracting and appending, or merging.

Table 1. Joining versus relating

Requirement

Joining

Relating

Access and analyze data from two tables simultaneously

Supported

Supported

Access and analyze data from more than two tables simultaneously

Not supported by a single join operation. Requires multiple join operations.

A single relation operation supports access/analysis of up to 18 tables simultaneously.

Outputs results to a new, physically separate ACL table

Yes

No

If required, you can perform a separate operation and extract any combination of fields from the related tables to a new table.

Key field data types must be identical for each table pair

Not required for character-numeric or numeric-character joins. Automatically harmonized by ACL. Required for all other possibilities.

Required

Key field lengths must be identical for each table pair

Recommended (not enforced)

Lengths of two character key fields automatically harmonized by ACL.

Recommended (not enforced)

Number of key fields

One or more key fields can be selected from each table.

Limited to one key field per table pair

If more than one key field is required to establish an accurate relation between a table pair, create a computed field in each table to concatenate the required key fields.

Execution speed of operation

Slower

The duration of the join operation varies depending on the complexity of the join, and whether or not the primary table is sorted.

Faster

No actual record matching is done during the relation operation. For this reason, it takes considerably less time than joining.

Subsequent processing of a file

Faster

The results of joining are stored in a flat file (.fil source data file). Flat files can be processed very quickly.

Slower

Record matching between related tables is done at the time of subsequent processing, which adds to processing time.

Required disk space for processing

More

Joining creates a new, third table that can be larger than both original tables combined, depending on the nature of the join.

Less

Minimal disk space is required to create an index for the child table(s).

Tables must be sorted or indexed

Sort, Presort, or Index required for secondary table, optional for primary table.

Index required for child tables (created automatically when relating tables), Sort or Index optional for primary table.

Updateable from source data files

No

Join results are sent to a new, third table with a new source data file no longer associated with the source data files involved in the join.

Yes

Related tables remain associated with, and can be refreshed from, the source data files involved in the relation.

Include primary records that have matching secondary records

Supported

Not directly supported

After relating tables, use filters to isolate primary records that have matching secondary records.

Include primary records that do not have matching secondary records

Supported

Not directly supported

After relating tables, use filters to isolate primary records that do not have matching secondary records.

Include all primary records whether or not they have matching secondary records

Supported

Supported

Include all secondary records whether or not they have matching primary records

Supported

Not supported

Include all primary and secondary records whether or not they have a match

Supported

Not supported

Include primary records that have matching secondary records, and any duplicate secondary matches (many-to-many matching)

Supported

Not supported

Related concepts
Common uses of joining or relating
About joining tables
About relating tables
Combining data


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