Add Join (Data Management)—ArcGIS AllSource

Summary

Joins a layer to another layer or table based on a common field. Feature layers, table views, and raster layers with a raster attribute table are supported.

The records in the Join Table parameter value will be matched to the records in the Input Table parameter value. A match is made when the input field and join field values are equal. This join is temporary.

Illustration

Usage

The Input Table parameter value can be a feature layer, a table view, or a raster layer with an attribute table. If a data path is used, the layer will be created with the join. The join will always reside in the layer, not with the data.
To make a permanent join, either use the Join Field tool or use the joined layer as input to one of the following tools: Copy Features, Copy Rows, Export Features, or Export Table. When saving the results to a new feature class or table, the Maintain fully qualified field names environment can be used to control whether the joined output field names will be qualified with the name of the table the field came from. Field aliases are persisted from the layer to the output, except when the output is a shapefile.
Use the Make Query Layer, Create Database View, or Make Aggregation Query Layer tools to optimize join performance and for more capabilities when you want to join enterprise geodatabase or SQLite database data.
If the input is a feature class or dataset path, this tool will create and return a new layer with the result of the tool applied.
When a one-to-many join is produced by the join, the result of the join can be viewed in the attribute table, where a warning message will indicate if the table has duplicate object IDs. Because many geoprocessing tools do not support data with duplicate object IDs and processing such data can produce unexpected results, it is recommended that you first copy the joined layer to a new feature class using the Export Features tool. Then use the new feature class as input to other geoprocessing tools.
Optionally, set the Join Operation parameter to Join one to first to prevent duplicate object IDs.
The Join Operation parameter has three states to adjust the cardinality. The default is blank and will allow the data source to attempt a one-to-many join. The Join one to many option will work only on specific data sources that have an Object ID field. The Join one to first option will use the first match in the table, which may result in different outputs if the Object ID field is changed or the workspace the table is copied to changes. One-to-first joins are not case sensitive; one-to-many joins are case sensitive.

The following tables include possible outcomes of performing a join with various inputs.

The first table shows a one-to-many join. Keeping only matching records will have no effect, as all records have matches.

Add Join example: One-to-many join when each table has an Object ID field
Input table		Join table		Result
Input field	Type	Join field	Value	Input field	Type	Join field	Value
1	A	1	100	1	A	1	100
2	B	2	200	2	B	2	200
		1	300	1	A	1	300
		2	400	2	B	2	400

The second table uses a join table with no Object ID field; only a one-to-first join is possible. A one-to-first join is also only possible if each table is from a different workspace. A one-to-first join is not a case sensitive match.

Add Join example: One-to-first join when either table does not have an Object ID field
Input table		Join table		Result
Input field	Type	Join field	Value	Input field	Type	Join field	Value
1	A	1	100	1	A	1	100
2	B	2	200	2	B	2	200
		3	300
		4	400

In the last table, the input table has more records than the join table. Keeping all records will keep all of the matching records plus the records from the input table that did not match.

Add Join example: One-to-many join when each table has an Object ID field and the Keep all input records parameter is checked
Input table		Join table		Result
Input field	Type	Join field	Value	Input field	Type	Join field	Value
1	A	1	100	1	A	1	100
2	B	2	200	2	B	2	200
3	C	1	300	1	A	1	300
4	D	2	400	2	B	2	400
				3	C	<Null>	<Null>
				4	D	<Null>	<Null>

The input table must have an Object ID field to perform a one-to-many join and be in the same workspace.

Records from the join table can be matched to more than one record if the join table has an Object ID field; otherwise, a one-to-first join will be performed.
When joining tables, the default option is to keep all records. If a record in the input table doesn't have a match in the join table, that record is given null values for all the fields being appended into the input table from the join table.
Input table Join table Result
Input field
Type
Join field
Value
Input field
Type
Join field
Value
1
A
1
100
1
A
1
100
2
B
2
200
2
B
2
200
3
C
1
300
1
A
1
300
4
D
2
400
2
B
2
400
3
C
<Null>
<Null>
4
D
<Null>
<Null>
When the Keep all input records parameter is unchecked, if a record in the input table doesn't have a match in the join table, that record is removed from the resultant output. If the input table is the attribute table of a layer, features that don't have data joined to them are not shown on the map.
Input table Join table Result
Input field
Type
Join field
Value
Input field
Type
Join field
Value
1
A
1
100
1
A
1
100
2
B
2
200
2
B
2
200
3
300
4
400
The Calculate Field tool will update the first record encountered when using a one-to-many layer and skip the remainder duplicate records. When editing the joined layer field values manually in the table view, the last edit made remains..
Field properties, such as aliases, visibility, and number formatting, are maintained when a join is added or removed.
The join persists only for the duration of the layer. A layer can be retained by saving the ArcGIS AllSource session or by saving it to a layer file using the Save Layer To File tool.

To see the results of a join created in a script tool, the tool must include the layer as a derived output parameter. Similarly, the Updated Input Layer or Table View parameter must be set as a derived output parameter in a model tool to see the joined results.
In the resulting table, fields will be prefixed with the input's name and a period (.), and all fields from the join table will be prefixed with the join table name and a period as the default.
For example, joining landuse, which has fields A and B, to lookup_tab, which has fields C and D, will result in a layer or table view with landuse.A, landuse.B, lookup_tab.C, and lookup_tab.D fields.
A layer must have unique field names. If both the input and join tables have the same name and are in different workspaces, a join cannot be preformed without creating a poorly defined layer.
Indexing the input field and join field can improve performance. If the Index join fields parameter is checked, an attribute index will be added to both fields. Alternatively, each joining field can be indexed with the Add Attribute Index tool.
If the join results are unexpected or incomplete, review whether the input field and join field are indexed. If the fields are not indexed, try adding an index. If the fields are indexed, try deleting and re-adding the index to correct any problems with the index. Alternatively, check the Rebuild join field indexes parameter to remove existing indexes and rebuild them.
If the input layer or table view's fields were modified (renamed or hidden) using the Field Info parameter in the Make Feature Layer or Make Table View tool, the field modifications will not be included in the output joined layer or table view.
The definition query of the join table will be applied to the input layer or table view by adding a new active query. The previous query is preserved and set to inactive so that the query can be disabled from the joined table if needed. The definition query can be removed using the Remove Join tool.
If the join table has a definition query, the Keep all input records parameter will have no effect. Manually updating the definition query by appending or OBJECTID is null can fix this if appropriate.
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.
Selections on the input or join tables are ignored in the Add Join tool. The Join Field tool supports selections. To only join with a selected subset, create a selection layer and use it as input to the Add Join tool. Join layer properties are copied when you create a selection layer.

Parameters

Label	Explanation	Data Type
Input Table	The layer or table view to which the join table will be joined.	Mosaic Layer; Raster Layer; Table View
Input Field	The field in the input layer or table view on which the join will be based.	Field
Join Table	The table or table view that will be joined to the input layer or table view.	Mosaic Layer; Raster Layer; Table View
Join Field	The field in the join table that contains the values on which the join will be based.	Field
Keep all input records (Optional)	Specifies whether only records in the input that match a record in the join table will be included in the output. Checked—All records in the input layer or table view will be included in the output. This is also known as an outer join. This is the default. Unchecked—Only those records in the input that match a row in the join table will be included in the output. This is also known as an inner join.	Boolean
Index join fields (Optional)	Specifies whether table attribute indexes will be added to the input field and join field. Checked—Both fields will be indexed. If the table has an existing index, a new index will not be added. Unchecked—Indexes will not be added. This is the default.	Boolean
Rebuild Joined Fields Indexes (Optional)	Specifies whether the indexes of the input field and join field will be removed and rebuilt. Checked—Existing indexes will be removed and a new index will be added. Unchecked—Existing indexes will not be removed or rebuilt. This is the default.	Boolean
Join Operation (Optional)	Specifies whether the join will be a one-to-many join or a one-to-first join when the data has a one-to-many cardinality. If no parameter value is specified, the join operation will be based on the data source. Join one to first—The join operation will use the first match. Join one to many—The join operation will perform multiple case-sensitive matches.	String

Derived Output

Label	Explanation	Data Type
Updated Input Layer or Table View	The updated input dataset.	Table View; Raster Layer; Mosaic Layer

arcpy.management.AddJoin(in_layer_or_view, in_field, join_table, join_field, {join_type}, {index_join_fields}, {rebuild_index}, {join_operation})

Name	Explanation	Data Type
in_layer_or_view	The layer or table view to which the join table will be joined.	Mosaic Layer; Raster Layer; Table View
in_field	The field in the input layer or table view on which the join will be based.	Field
join_table	The table or table view that will be joined to the input layer or table view.	Mosaic Layer; Raster Layer; Table View
join_field	The field in the join table that contains the values on which the join will be based.	Field
join_type (Optional)	Specifies whether only records in the input that match a record in the join table will be included in the output. KEEP_ALL—All records in the input layer or table view will be included in the output. This is also known as an outer join. This is the default. KEEP_COMMON—Only those records in the input that match a row in the join table will be included in the output. This is also known as an inner join.	Boolean
index_join_fields (Optional)	Specifies whether table attribute indexes will be added to the input field and join field. INDEX_JOIN_FIELDS—Both fields will be indexed. If the table has an existing index, a new index will not be added. NO_INDEX_JOIN_FIELDS—Indexes will not be added. This is the default.	Boolean
rebuild_index (Optional)	Specifies whether the indexes of the input field and join field will be removed and rebuilt. REBUILD_INDEX— Existing indexes will be removed and a new index will be added. NO_REBUILD_INDEX— Existing indexes will not be removed or rebuilt. This is the default.	Boolean
join_operation (Optional)	Specifies whether the join will be a one-to-many join or a one-to-first join when the data has a one-to-many cardinality. If no parameter value is specified, the join operation will be based on the data source. JOIN_ONE_TO_FIRST—The join operation will use the first match. JOIN_ONE_TO_MANY—The join operation will perform multiple case-sensitive matches.	String

Derived Output

Name	Explanation	Data Type
out_layer_or_view	The updated input dataset.	Table View; Raster Layer; Mosaic Layer

Code sample

AddJoin example 1 (Python window)

The following Python window script demonstrates how to use the AddJoin function in immediate mode.

import arcpy
arcpy.env.workspace = "C:/data/Habitat_Analysis.gdb"
veg_joined_table = arcpy.management.AddJoin("vegetation", "HOLLAND95", 
                                            "vegtable", "HOLLAND95")
arcpy.management.CopyFeatures(veg_joined_table, "vegjoin")

AddJoin example 2 (stand-alone script)

This stand-alone script shows the AddJoin function as part of a workflow to join a table to a feature class and to extract specified features.

# Name: AttributeSelection.py
# Purpose: Join a table to a feature class and select the desired attributes

# Import system modules
import arcpy

# Set environment settings
arcpy.env.workspace = "C:/data/Habitat_Analysis.gdb"
# The qualifiedFieldNames environment is used by Copy Features when persisting 
# the join field names.
arcpy.env.qualifiedFieldNames = False

# Set local variables
inFeatures = "vegtype"
joinTable = "vegtable"
joinField = "HOLLAND95"
expression = "vegtable.HABITAT = 1"
outFeature = "vegjoin"

# Join the feature layer to a table
veg_joined_table = arcpy.management.AddJoin(inFeatures, joinField, joinTable, 
                                            joinField)

# Select desired features from veg_layer
arcpy.management.SelectLayerByAttribute(veg_joined_table, "NEW_SELECTION", 
                                        expression)

# Copy the layer to a new permanent feature class
result = arcpy.management.CopyFeatures(veg_joined_table, outFeature)

# See field names and aliases
resultFields = arcpy.ListFields(result)
print([field.name for field in resultFields])
print([field.aliasName for field in resultFields])

Environments

Current Workspace

Summary

Illustration

Usage

Parameters

Derived Output

Derived Output

Code sample

Environments

Related topics

In this topic