What is the difference between joining and relating tables

In addition, the number of values do not need need to match in both tables. Just like it doesn't matter the number of matching records, the number of values in both tables do not need to match.

One field can contain 27 values, the other can contain values, and only 5 of those values can successfully match and it makes no difference. One thing that should be noted when it comes to table joins and relates is the fact that these are almost never completed on the OID object identifier or the FID feature identifier.

We learned in the section about databases that each table contains at least one unique value per row, and we call that row the OID or FID. These values are, in general, completely random. For example, if you intend to keep the FID in a particular order while digitizing a series of roads and you skip one on accident, in order to keep the data complete Chapter Eight , you will need to go back digitize that missing road.

The series of values in the FID field will continue where it left off and your intended series of values will be broken. The solution? Create a field where a value is associated with each road, just like the player ID we saw in the database example.

This value will have nothing to do with the FID, meaning you can go back and complete things like table joins, relates, and select by attribute based on this value. Once a key is established in a table join or relate, the next step is to establish a relationship - one-to-one , one-to-many , many-to-one , and many-to-many.

In ArcMap, this relationship is often implied based upon which tables are chosen to be joined and the order in which they are joined - that is to say the relationship is determined by ArcMap based upon which table goes first in the table join tool and which table goes second.

Relates are bi-directional since the fields are not appended, the software just shows the related field in each table via highlighting them. The relationship type still exists, but there isn't a specific need to establish it, since nothing is being altered in the tables. Lab will explore this idea, placing one table first in the tool and examining the result, then flipping the tool, putting the second table first and examining that result.

This data exploration will help solidify the idea of implied relationships in ArcGIS. Even though these relationships are implied with table joins and relates, it is still important to understand the types of table relationships, as there are some non tools which require you to establish the desired relationship yourself as well as understanding the results of the table joins completed in Introduction to GIS.

In a one-to-one relationship seen in the image above , each feature has only one matching value based upon the selected key, and thus one row. For example, if you were to join a state capitals data table with a states layer, each state would have only one capital, and each capital would associate with only one state.

The Many-To-One Relationship are between several features and one row in a data table. The example seen the the image to the right shows a feature layer aka a vector file, a layer with features with two different cover codes, as designated with a coded value.

The table which is being joined contains both the coded and "decoded" values we don't really used the word 'decoded', but instead 'matched'. One-To-Many Relationships have a single feature which relates to two or more rows in a data table. Some non-spatial examples are: one person can play many positions on a baseball team, one person can own two or more cars, or many visitors visit one emergency room which can also be a spatial relationship!

Spatial examples might be, one state can have several counties Colorado has 64 counties and one water sampling location has many samples.

Fictitious water sampling station A has hourly samples taken, which are averaged together and contained in a table which shows daily, weekly, and monthly sample average along side the hourly sample taken. Many-To-Many are relationships where two or more features match to two or more rows in the data table. A non-spatial example of a many-to-many relationship is the academic roster of the college. A spatial example of a many-to-many relationship would be speed limits and road names.

Many times, as you drive along on physical road the geometry of the road , it will change names and speed limits several times. So far, we've looked at what table joins and relates are, what a key is and it's requirements, and four types of table relationships.

In this section, we are going to take a minute to note how we initiate a table join or relate, what validating a table join or relate does, and what happens after a successful table join. Again, this isn't for you to memorize right now, but instead to have you quickly look at how we perform table joins and relates, what and why we would want to validate said table join or relate before the actual action occurs, and what to expect after a successful table join.

All of these skills will be practiced and solidified in lab, but it's a good idea to take a quick look at the process prior to lab to understand what is happening with this particular ArcGIS tool. In ArcMap, we will interact with several " Something Options" menus, named based on the place from which the menu is launched. We looked at the Table Options menu earlier in the chapter, which was a menu launched from an attribute table and containing actions which affect an entire table. This command asks you to confirm the action, because you cannot undo removing all joins.

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. There are multiple players per team and each team is unique. A table relate creates an entirely new table. For example, if we select the Houston Texans, only those players on that team will show up in a new table. First, you need to click the table drop-down button in the top-left of the attribute table as shown below. 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. The fields do not have to share the same name, but they must have the same data types e. To join two datasets:. Unlike joining tables, relating two tables simply defines a relationship between them.

The associated data aren't appended to the layer's attribute table as they are with a join. Instead, you can access the related data when you work with the layer's attributes.