Join Field (Data Management)

This ArcGIS 3.0 documentation has been archived and is no longer updated. Content and links may be outdated. See the latest documentation.

Summary

Permanently joins the contents of a table to another table based on a common attribute field. The input table is updated to contain the fields from the join table. You can select which fields from the join table will be added to the input table.

The records in the Input Table value are matched to the records in the Join Table value based on the values of the Input Join Field and Join Table Field parameters. Optionally, you can select specific fields from the Join Table value to be appended to the Input Table value during the join.

Usage

  • The Input Table value can be a feature class (including a shapefile) or a table.

  • All fields in the Input Table value will be kept during the join. Optionally, you can select specific fields from the Join Table value to be added to the output. You add these using the Transfer Fields parameter.

  • Records from the Join Table value can be matched to more than one record in the Input Table value.

  • If no fields are selected for the optional Transfer Fields parameter, all fields from the Join Table value will be joined to the output.

  • Joins can be based on fields of type text, date, or number.

  • Joins based on text fields are case sensitive.

  • Fields of different number formats can be joined as long as the values are equal. For example, a field of type float can be joined to a short integer field.

  • The Input Join Field value and the Join Table Field value can have different field names.

  • If a join field has the same name as a field from the input table, the joined field will be appended with _1 (or _2, or _3, and so on) to make it unique.

  • If values in the Join Table Field value are not unique, only the first occurrence of each value will be used.

    • To account for join table values other than the first occurrence, start by running the Summary Statistics tool using the Join Table value as input. Summary Statistics allows you to summarize fields (for example, sum, mean, minimum).
    • To merge two or more fields in the join table before the join, first export the table or feature class using the Table To Table tool. Then merge them using the tool's field map.
  • To manage the fields in the output dataset and the contents of those fields, use the Field Map parameter.

    • To change the field order, select a field name and drag it to the new position.
    • The default data type of an output field is the same as the data type of the first input field (of that name) it encounters. You can manually change the data type at any time to any other valid data type.
    • The available merge rules are first, last, join, sum, mean, median, mode, minimum, maximum, standard deviation, and count.
    • When using the Join merge rule, you can specify a delimiter such as a space, comma, period, dash, and so on. To use a space, ensure that the pointer is at the start of the input box and press the Spacebar once.
    • You can specify the start and end positions of text fields using the format option.
    • Do not perform standard deviation on a single input because values cannot be divided by zero, so standard deviation is not a valid option for single inputs.

    Learn more about mapping fields in scripts

  • The Validate Join tool can be used to validate a join between two layers or tables to determine if the layers or tables have valid field names and Object ID fields, if the join produces matching records, if the join is a one-to-one or one-to-many join, and other properties of the join.

    A button to validate the join is available on the tool dialog box for ease of use.

Parameters

LabelExplanationData Type
Input Table

The table or feature class to which the join table will be joined.

Mosaic Layer; Raster Layer; Table View
Input Join Field

The field in the input table on which the join will be based.

Field
Join Table

The table to be joined to the input table.

Mosaic Layer; Raster Layer; Table View
Join Table Field

The field in the join table that contains the values on which the join will be based.

Field
Transfer Fields
(Optional)

The fields from the join table to be transferred to the input table, based on a join between the input table and the join table.

Field
Transfer Method
(Optional)

Specifies how joining fields and field types will be transferred to the output.

  • Select transfer fieldsFields and field types from the joined table will be transferred to the output. This is the default.
  • Use field mappingThe transfer of fields and field types from the joined table to the output will be controlled by the Field Map parameter.
String
Field Map
(Optional)

The attribute fields that will be in the output with the corresponding field properties and source fields. By default, all fields from the inputs will be included.

Fields can be added, deleted, renamed, and reordered, and you can change their properties.

Merge rules allow you to specify how values from two or more input fields are merged or combined into a single output value. There are several merge rules you can use to determine how the output field will be populated with values.

  • First—Use the input fields' first value.
  • Last—Use the input fields' last value.
  • Join—Concatenate (join) the input field values.
  • Sum—Calculate the total of the input field values.
  • Mean—Calculate the mean (average) of the input field values.
  • Median—Calculate the median (middle) of the input field values.
  • Mode—Use the value with the highest frequency.
  • Min—Use the minimum value of all the input field values.
  • Max—Use the maximum value of all the input field values.
  • Standard deviation—Use the standard deviation classification method on all the input field values.
  • Count—Find the number of records included in the calculation.

Field Mappings

Derived Output

LabelExplanationData Type
Updated Input Table

The updated input dataset.

Table View; Raster Layer; Mosaic Layer

Licensing information

  • Basic: Yes
  • Standard: Yes
  • Advanced: Yes

Related topics