Comparison of data combining methods

The advantages and disadvantages of the different data combining methods in Analytics are outlined below.

Note

Appending, extracting and appending, and merging are compared because these methods combine tables with identical or similar record structures.

Joining is compared with relating because these two methods combine tables with different record structures.

For more information, see Data structure and data format requirements.

Appending, extracting and appending, and merging

Requirement/Capability	Appending	Extracting and Appending	Merging
Tables being combined must have an identical data structure	No	Yes	Yes
Resulting combined table is sorted	No Records extracted from source tables are appended as groups in the output table.	No Records extracted from the source table are appended as a group to the end of the target table.	Yes Records from both tables are inserted into a new, third table based on a sort order.
Access and analyze data from two tables	Yes	Yes	Yes
Access and analyze data from more than two tables	Yes	Not supported by a single extract and append operation. Requires multiple operations.	Not supported by a single merge operation. Requires multiple operations.
Outputs results to a new, physically separate Analytics table	Yes	No	Yes
Key fields in both tables must be: sorted the same data type the same length	Not applicable Appending does not use key fields.	Not applicable Extracting and appending does not use key fields.	Yes
Number of key fields	Not applicable Appending does not use key fields.	Not applicable Extracting and appending does not use key fields.	One or more key fields can be selected from each table.

Joining and relating

Note

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

Comparison of capabilities

Capability	Joining	Relating
Use case	Good for as a preliminary step for investigative work because it outputs a permanently joined new third table.	Good for informational work because it creates a virtual table without any requirement that it be made permanent.
Access and analyze data from two tables simultaneously	Yes	Yes
Access and analyze data from more than two tables simultaneously	No Not supported by a single join operation. Requires multiple join operations.	Yes A single relation operation supports access/analysis of up to 18 tables simultaneously.
Outputs results to a new, physically separate Analytics 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.
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.
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.
Matched primary and secondary records (1st secondary match)	Yes	Not directly supported After relating tables, use filters to isolate primary records that have matching secondary records.
Matched primary and secondary records (all secondary matches) Also called many-to-many matching	Yes	No
Unmatched primary records	Yes	Not directly supported After relating tables, use filters to isolate primary records that do not have matching secondary records.
All primary records and matched secondary records	Yes	Yes
All secondary records and matched primary records	Yes	No
All primary and secondary records, matched and unmatched	Yes	No

Comparison of requirements

Requirement	Joining	Relating
Tables being combined must have an identical data structure	No	No
Key field data types must be identical for each table pair	Varies Not required for character-numeric or numeric-character joins. Automatically harmonized by Analytics. Required for all other possibilities.	Yes
Key field lengths must be identical for each table pair	Recommended (not enforced) Lengths of two character key fields automatically harmonized by Analytics.	Recommended (not enforced)
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.