What’s the difference between relate and join for tables in ArcGIS?
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 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 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 name.
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 did appending 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.
Relate Example 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 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 in 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 in 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 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).
- One-to-one relationships (1-1)
- One-to-many relationships (1-M)
- Many-to-many relationships (M-N)
For any type of database, one-to-one relationships have one matching records 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 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 for tables 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 principal of having matching unique IDs in two tables.
While join is particularly useful for 1-1 relationships, relate is ideal for 1-M relationships.
Do you have any questions about relate vs join? Let us know with a comment below.