Overview of joins and relates

Through a common field, known as a key, you can associate records in one table with records in another table. For example, you can associate a table of parcel ownership information with the parcels layer, because they share a parcel identification field. You can make these associations in several ways, including by joining or relating tables temporarily in your map or by creating relationship classes in your geodatabase that maintain more permanent associations. Joins can also be based on spatial location.

Joins and related information are stored in the layer's properties. To access this information, double-click the layer to open Layer Properties, and select Joins or Relates.

Join the attributes from a table

Suppose you have obtained data that describes the percentage change in population by county, and you want to generate some population growth maps based on this information. As long as the population data is stored in a table in your database and shares a common field with your layer, you can join it to your geographic features and use any of the additional fields to symbolize, label, query, or analyze the layer's features.

Typically, you'll join a table of data to a layer based on the value of a field that can be found in both tables. The name of the field does not have to be the same, but the data type must be the same; you join numbers to numbers, strings to strings, and so on. You can perform a join using the Add Join geoprocessing tool. When performing an attribute join, the joined fields are dynamically added to the existing table. Field properties—such as aliases, visibility, and number formatting—are maintained when a join is added or removed.

Depending on how your data is organized, you may want to start by summarizing the data in your table before you join it to a layer. When you summarize a table, a new static table containing the summary statistics is derived. During this process, you can create various summary statistics including count, average, sum, minimum, and maximum. For example, suppose you want to create a population map by state instead of county, but the data you have is organized by county. By summarizing the county data by state—in this example, finding the total sum for each state population—and then joining that newly created output table to a state layer, you can create a population map by state.

When using data where a one-to-many or many-to-many relationship exists, use a relate or relationship class to establish the relationship between the datasets. However, you can create a join under these circumstances. When you create a join in such a case, there are differences between how tools and other layer-specific settings work depending on the data source. If you are using data in the same geodatabase to create the join, all matching records are returned. If you are using nondatabase data, such as shapefiles or dBASE tables, to create the join, only the first matching record is returned.

Join data by location (spatially)

When the layers on your map don't share a common attribute field, you can join them using the Spatial Join geoprocessing tool, which joins the attributes of two layers based on the location of the features in the layers.

With a spatial join, you can complete any of the following common workflows:

Join by location, or spatial join, uses spatial associations between the layers involved to append fields from one layer to another. Depending on the type of association, you can append the attributes or an aggregate (minimum, maximum, mean, and so on) of numeric attributes, from a matched feature to the target features.

Spatial joins by default are different from attribute-driven joins, relates, and relationship classes, in that they are not dynamic and require you to save the results to a new output layer. If you use the Spatial Join tool in a script or model, you can improve performance using an in-memory workspace, instead of creating output. For example, to write to an in-memory workspace, the output path of the tool includes the workspace and the name of the output, such as in_memory/outputFeature.

Remove a join

To remove a join, use one of the previously mentioned methods to access the Joins menu items, and open the Remove Join tool. You can use the menu on an open attribute table, the Data tab for a layer or stand-alone table selected in the Contents pane, or the Joins and Relates context menu.

From the Joins menu, you can also choose to remove all joins. This command asks you to confirm the action, because you cannot undo removing all joins.

Relate the attributes in one table to another

Relates can help you discover specific information in your data. For example, if you select a building, you can find all the tenants that occupy that building. Similarly, if you select a tenant, you can find what building they reside in (or several buildings, in the case of a chain of stores in multiple shopping centers—a many-to-many relationship). A relate or relationship class is recommended when using data where a one-to-many or many-to-many relationship exists.

Unlike joining tables, relating tables defines a relationship between two tables. The associated data isn't appended to the layer's attribute table like it is with a join. Instead, you can access the related data through selected features or records in your layer or table. You can create a relate using the Add Relate geoprocessing tool.

Relates that are added to a layer or table in a map are essentially the same as simple relationship classes defined in a geodatabase, except that they are saved with the map instead of in a geodatabase. A relationship class stores information about associations among features and records in a geodatabase and can help ensure your data's integrity. To create a relationship class, use the Create Relationship Class tool or right-click the geodatabase in the Catalog pane, point to New, and click Relationship.

If a feature class in a geodatabase already participates in a relationship class, you don't need to create a relate for the tables. It is already available for use and listed in the Related Data menu that you can use to view related data. Note that the many-to-many relationship is defined differently when your data is stored in a geodatabase.

Remove a relate

To remove a relate, use one of the previously mentioned methods to access the Relates menu items and open the Remove Relate tool. You can use the menu on an open attribute table, the Data tab for a layer or stand-alone table selected in the Contents pane, or the Joins and Relates context menu.

From the Relates menu, you can also choose to remove all relates. This command asks you to confirm the action because you cannot undo removing all relates.

Related topics