About joining and relating tables
Last modified April 24, 2009
Print all topics in : "Creating tables and managing attribute information"
This topic was updated for 9.3.1.
Most database design guidelines promote organizing your database into multiple tables—each focused on a specific topic—instead of one large table containing all the necessary fields. Having multiple tables prevents duplicating information in the database because you store the information only once in one table. When you need information that isn't in the current table, you can link the two tables together.
For example, you might obtain data from other departments in your organization, purchase commercially available data, or download data from the Internet. If this information is stored in a table, such as a dBASE, INFO, or geodatabase table, you can associate it with your geographic features and display the data on your map.
ArcMap provides two methods to associate data stored in tables with geographic features: joins and relates. When you join two tables, you append the attributes from one onto the other based on a field common to both. Relating tables defines a relationship between two tables—also based on a common field—but doesn't append the attributes of one to the other; instead, you can access the related data when necessary.
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 has to be the same; you join numbers to numbers, strings to strings, and so on. You can perform a join with either the Join Data dialog box accessed by right-clicking a layer in ArcMap or the Add Join geoprocessing tool.
Suppose you obtain daily weather forecasts by county and generate weather maps based on this information. As long as the weather 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.
When the layers on your map don't share a common attribute field, you can join them using a spatial join, which joins the attributes of two layers based on the location of the features in the layers. You can perform a spatial join with either the Join Data dialog box, accessed by right-clicking a layer in ArcMap, or the Spatial Join geoprocessing tool.
With a spatial join, you can find
Unlike joining tables, relating tables simply 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 when you work with the layer's attributes.
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 it resides in (or several buildings, in the case of a chain of stores in multiple shopping centers—a many-to-many relationship). However, if you performed a join on such data, ArcMap will only find the first tenant belonging to each building, ignoring additional tenants.
Relates defined in ArcMap 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.
If your data is stored in a geodatabase and has relationship classes defined, you can use these directly without having to establish a relate in ArcMap. The relationship classes will automatically be available when you add a layer that participates in a relationship class to the map. Note that the many-to-many relationship is defined differently when your data is stored in a geodatabase. In general, if you have relationship classes defined in your geodatabase, you should use these instead of creating new ones in ArcMap.
Learn how to create a relationship class.
A relationship class stores information about associations among features and records in a geodatabase and can help ensure your data's integrity.
Learn more about the benefits of using relationship classes.
Follow these general guidelines when choosing between joins and relates on your data:
When you save a map containing joins and relates, ArcMap saves the definition of how the two attribute tables are linked rather than saving the linked data itself. The next time you open your map, ArcMap reestablishes the relationship (whether a join or relate) between the tables by reading the tables from the database. In this way, any changes to the source tables that have taken place since they were last viewed on the map are automatically included and reflected on the map.
Joins can be stored in an ArcMap document or in a layer file. If you plan on moving the data at some point, you should save your ArcMap documents with relative paths. If data is moved, you can repair tables and layers after opening the document, but unless the target table and the join tables are in the same directory or workspace, the joins will not be repaired. If you save your document with relative paths, tables and layers will be restored automatically with joins as long as the document has been moved relative to where the data has been moved.
You can make a permanent disk copy of a layer with joined data simply by exporting the layer. To export the layer, right-click it in the table of contents, point to Data, and click Export Data. This creates a new feature class with all of the attributes including the joined fields.
If your data is involved in both joins and relates, the order in which the joins and relates are created is significant. If your layer or table has a relate, it is removed once data is joined to it. If you perform a relate on a joined layer or table, the relate is removed when the join is removed. As a general rule of thumb, it is best to create your joins and then add your relates.
In a situation where you need to join tableA and relate tableB to layerC, two of the three possible ways of doing this will work. The following describes each case: