Relate vs Join: What’s the Difference in ArcGIS?
A “Join” combines attribute tables based on a common field, while a “Relate” establishes a temporary link between two tables to explore their relationships without merging the actual data.
What’s the difference between “Relate” and “Join”?
When you have matching unique IDs in two tables, you can create a table relate or table join.
A Table Join appends all the columns from one table into the other table based on the unique ID.
But a Table Relate creates an entirely new table. So when you select the record(s) in one table, it will create a temporary table based on all the matching unique IDs.
When do you use relate? And when do you use a “join”? Let’s learn with 2 examples.
Join Example with 1:1 Relationship
Joins are suitable for 1:1 relationships. So when you have a single record that matches in both tables, this is an appropriate time to use a join.
For example, we have a shapefile of cities in the United States. And we also have a spreadsheet of NFL teams with city and team names.
As long as we don’t have two cities with the same name, that means that “NAME” is unique in the “US_Cities” shapefile. And this also means we have a 1:1 relationship because there are no rows with the same unique ID twice or more.
This is the perfect situation to perform a “Join”. When you create a join between these two tables, it appends the “NFL_Team” column into the “US_Cities” shapefile.
In ArcGIS, how can we perform “joins” like we appended NFL team names to the US cities’ shapefile?
First, click the table drop-down button and select “Joins and Relates” > Join. From here, you have to select the fields from both tables with the unique identifier. In our case, it’s the “NAME” and “CITY”.
If we export this table after it’s been joined, then it becomes permanent. This means that the appended columns will remain in the new table or feature class.
Here’s another example of a 1:1 join for Texas counties and a population table in ArcGIS Pro. Because both tables have the FIPS identifier, it’s possible to transfer fields from one table to another table.
Relate Tool with 1:M Relationship
When one record has multiple matching IDs in another table, this is a one-to-many relationship (1-M). And it’s best to use “relate” for 1-M relationships.
For example, let’s say we have a list of football teams and players. There are multiple players per team and each team is unique. So this means that it’s 1-M.
A Table Relate creates an entirely new table. So when you select the record(s) in one table, it will create a temporary table based on all the matching unique IDs.
For example, if we select the Houston Texans, only those players on that team will show up on a new table.
To set up a “relate”, we open the “US_Teams” attribute table. First, you need to click the table drop-down button in the top-left of the attribute table (as shown below).
From the drop-down, select “Joins and Relates” > Relate… Now, select the columns that match both tables. In our case, it’s “NAME” and “NAME”. Finally, select the default name “Relate1” or give it another name.
Now that we’ve identified the unique IDs in both tables, we can select the team(s) in the football team table. After we click “Related tables” > Relate1, a new table opens with all the matching records for the team(s) we’ve selected.
Cardinality: 1-1 Relationships vs 1-M Relationships
There are 3 types of relationships (or cardinality).
For any type of database, one-to-one relationships have one matching record in two tables. Next, one-to-many relationships have multiple records in one table that match a single record in another. Lastly, many-to-many relationships have multiple matching records in both tables.
In all cases, the unique identifiers (or keys) have to exist in both tables for any type of relationship. This unique ID (primary key) is the primary linkage between the related table (foreign key).
Table Relate vs Table Join in ArcGIS
While join appends attributes to the end of a table, relate creates a temporary table for all the matching records.
All tables link with each other based on cardinality (1-1, 1-M, and M-N). Both join and relate work on the principle of having matching unique IDs in two tables.
While join is particularly useful for a 1-1 relationship, relate is ideal for a 1-M relationship. If you don’t have any unique fields to join, you can try a spatial join. This process uses location or proximity to join fields.
Do you have any questions about relate vs join? Let us know with a comment below.
