Essentials of joining tables

Joining data is typically used to append the fields of one table to those of another through an attribute or field common to both tables. You can choose to define the join based on either attributes or a predefined geodatabase relationship class or by location (also referred to as a spatial join). You will only see join by relationship class listed if you are joining geodatabase data for which a relationship class has already been defined in the geodatabase.

Several tables or layers can be joined to a single table or layer, and relationship class joins can be mixed with attribute joins. When a join table is removed, all data from tables that were joined after it is also removed, but data from previously joined tables remains. Symbology or labeling that is based on an appended column is returned to a default state when the join is removed.

In most cases, appended columns are named <TableName>.<FieldName>. This naming convention helps prevent duplicate field names when the target table and a join table have common field names. If you do not want to see the full field names like that, click the Table window's Table Options button Table Options and click Show Field Aliases to toggle this option on or off. When this option is on, a check mark Show Field Aliases is displayed beside it on the Options menu, and your fields are not prefixed with the table name.

The following is a joined table with the field names prefixed with the table name:

Joined table showing the field names prefixed with the table name

The following is a joined table with only the field alias showing:

Joined table showing just the field names

Learn more about joining and relating tables

Summarizing your data before joining it

Depending on how your data is organized, you may have to start by summarizing the data in your table before you join it to a layer. When you summarize a table, ArcMap creates a new table containing summary statistics derived from your table. You can create various summary statistics including count, average, sum, minimum, and maximum.

For example, suppose you want to create weather maps by state instead of county, but the weather information you have is organized by county. You could summarize the county data by state—for instance, finding the average rainfall for all counties within a state—then join the newly created output table to a state layer to create a weather map of rainfall by state.

Example of summarizing tabular data so that it can be joined to geographic data

Editing and joining tables

NoteNote:

When editing joined data, you cannot edit the joined columns directly. To edit the joined data, you must first add the joined tables or layers to ArcMap. You can then perform edits on this data separately. These changes will be reflected in the joined columns.

Join validation

You can analyze a join before creating it by using the Validate Join button on the Join Data dialog box. Join validation allows you to assess any potential problems that you might encounter when creating a join. Join validation analyzes the two participating datasets to determine if there are any common problems with the data. The following is a list of what is checked in the data:

Each of these four problems can cause join fields to display null values in the attribute table or cause selection and record counts to be misleading. Join validation excludes the character symbols the number sign (#), the dollar sign ($), and the hyphen (-) for the invalid characters check when analyzing coverage data and excludes the period (.) when checking for invalid characters for ArcSDE software-connected data. You will still receive a warning if a field name begins with any of these characters.

Join validation checks for the following characters:

Invalid starting characters: `~@#$%^&*()-+=|\\,<>?/{}.!'[]:;_0123456789

Invalid contained characters: `~@#$%^&*()-+=|\\,<>?/{}.!'[]:;

Join validation also informs you about how many records will match if the join is created. You can compute what percentage of records were successfully matched and determine if there might be other errors in the data if the number of matched records is not what was expected. This could occur if using text fields to create a join and an expected matching record has a spelling mistake or an uppercase or lowercase character that causes no match to be found. If join validation counts more matched records than there are records in the source dataset, a warning is displayed that a 1:M or M:M relationship exists between the participating data and you should not use a join to associate these datasets to each other; instead, you should use a relate or relationship class.

Example of how Join Validation works

Performance tips for joining data

Data from appended fields can be used to symbolize and label features and perform queries and many other operations. Accessing the joined data is slower than accessing data from the target table because of the additional work needed to maintain the join.

The following tips can be used when working with joined data to improve performance:

Reasons joining tables may fail

After performing a join, the values in the fields from the joined table might appear empty or null. Null values can be the result of several factors:

Related Topics


9/17/2012