Add Join (Data Management)

Summary

Joins a layer to another layer or table (where 'layer' is a feature layer, table view, or raster layer with a raster attribute table) based on a common field.

The records in the Join Table are matched to the records in the input Layer Name. A match is made when the input join field and output join field values are equal. This join is temporary.

Illustration

Usage

• The input must be a feature layer a table view or a raster layer that has an attribute table; it cannot be a feature class or table.

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

• The join lasts only for the duration of the session. To persist the join for use in another session, save the layer to a layer file using the Save Layer To File tool. This only applies to layers; table views cannot be saved in this manner.

  To make a permanent join, consider using the Join Field tool. Another way to make the join permanent is to save the joined feature layer to a new feature class or the joined table view to a new table.

  • In ArcMap, right-click the input layer or table view; select Data > Export Data; specify the output feature class, shapefile, table, or dBASE table.
  • If the input is a feature layer, use the Copy Features tool; if the input is a table view, use the Copy Rows tool.

• This tool is not limited to ArcMap; it works on layers and table views in ArcCatalog and in scripts. The Make Feature Layer tool makes a layer from a feature class, and the Make Table View tool creates a table view from an input table or feature class.

• The Join Table can be any of the following types of tables: a geodatabase table (ArcSDE, file, or personal), a dBASE file, an INFO table, or an OLE DB table.

• The input layer or table view must have an ObjectID field. The Join Table is not required to contain an ObjectID field.

• Indexing the fields in the input layer or table view and Join Table on which the join will be based can improve performance. This can be done with the Add Attribute Index tool or by right-clicking the input in ArcCatalog and using the dialog box to add an index to the desired field.

• In the resulting layer or table view, the fields from the input layer or table view 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 plus 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 the following fields: "landuse.A", "landuse.B", "lookup_tab.C", and "lookup_tab.D".
  • For coverage feature classes and INFO tables, the table and field name separator is a colon (:) instead of a period.

• You can save the results of Add Join to a new feature class by using the Copy_Features or Copy_Rows tool. When saving results using these copy tools, you can use the Qualified Field Names environment to control if the joined output field names will be qualified with the name of the table the field came from.

• Records from the Join Table can be matched to more than one record in the input layer or table view.

• If the input layer or table view's fields were modified (renamed or hidden) using the Make Feature Layer or Make Table View tool Field Info parameter, these field modifications will not be carried forward to the output joined layer or table view.

• The Join Table name cannot start with a numeric value.

Syntax

AddJoin_management (in_layer_or_view, in_field, join_table, join_field, {join_type})

Parameter	Explanation	Data Type
in_layer_or_view	The layer or table view to which the join table will be joined.	Mosaic Layer; Raster Catalog 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 to be joined to the input layer or table view.	Mosaic Layer; Raster Catalog 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 what will be done with records in the input that match a record in the join table. KEEP_ALL —All records in the input layer or table view will be included in the output; also known as an outer join. This is the default. KEEP_COMMON —Only those records in the input that match to a row in the join table will be present in the result; also known as an inner join.	Boolean

Code Sample

AddJoin Example (Python Window)

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

import arcpy
from arcpy import env
env.workspace = "C:/data"
arcpy.MakeFeatureLayer_management ( "Habitat_Analysis.gdb/vegtype", "veg_layer")
arcpy.AddJoin_management( "veg_layer", "HOLLAND95", "vegtable.dbf", "HOLLAND95")
arcpy.CopyFeatures_management( "veg_layer", "Habitat_Analysis.gdb/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 then extract desired features.

# Name: AttributeSelection.py
# Purpose: Join a table to a featureclass and select the desired attributes
# Author: ESRI

# Import system modules
import arcpy
from arcpy import env


try:
    # Set environment settings
    env.workspace = "C:/data"
    env.qualifiedFieldNames = False
    
    # Set local variables    
    inFeatures = "Habitat_Analysis.gdb/vegtype"
    layerName = "veg_layer"
    joinTable = "vegtable.dbf"
    joinField = "HOLLAND95"
    expression = "vegtable.HABITAT = 1"
    outFeature = "Habitat_Analysis.gdb/vegjoin"
    
    # Create a feature layer from the vegtype featureclass
    arcpy.MakeFeatureLayer_management (inFeatures,  layerName)
    
    # Join the feature layer to a table
    arcpy.AddJoin_management(layerName, joinField, joinTable, joinField)
    
    # Select desired features from veg_layer
    arcpy.SelectLayerByAttribute_management(layerName, "NEW_SELECTION", expression)
    
    # Copy the layer to a new permanent feature class
    arcpy.CopyFeatures_management(layerName, outFeature)
    
except Exception, e:
    # If an error occurred, print line number and error message
    import traceback, sys
    tb = sys.exc_info()[2]
    print "Line %i" % tb.tb_lineno
    print e.message

Environments

Auto Commit, Current Workspace, Qualified Field Names

Licensing Information

ArcView: Yes

ArcEditor: Yes

ArcInfo: Yes