FAQ: Is it possible to identify mismatched records for a join operation?
Is it possible to identify mismatched records for a join operation?
Yes, it is possible to identify mismatched records for a join operation. Before finalizing the join operation, validate the join with the Validate Join function to see the number of mismatched records.
If the dataset is small, the mismatched records can be identified by manually searching the joined table. However, for larger datasets, this process can be time-consuming and tedious.
The following are two methods to identify mismatched records in large datasets after a join is performed.
Note: Both methods require the user to select the option Keep all records instead of Keep only matching records when performing the join operation. This is to ensure the mismatched records are included in the join.Option A: Using the Select By Attribute tool
- Open the joined table, and navigate to Table Options > Select By Attributes.
- Create a query by selecting the joined field, followed by the operators IS and NULL.
- The mismatched records are now displayed.
Option B: Using the Relate tool
This step requires an exported layer containing a join. Remove any existing joins between datasets before proceeding with these steps.
Note: To make a join permanent, export the data to a layer.
- Right-click the exported join layer > Joins and Relates > Relate.
- Select the desired fields and name for the relate operation. Ensure the correct layer is selected for the desired criteria.
- After the relate is created, open the attribute table of the layer selected in step 2.
- Select all the records in the table, and click the Related Tables option. A new tab is opened at the bottom of the Table dialog box.
- Reverse the selection with the Switch Selection option, and the results returned are the records that are mismatched.
- ArcGIS Help: Essentials of joining tables
- ArcGIS Help: About joining and relating tables
- ArcGIS Help: Essentials of relating tables
- ArcGIS Help: Relating the attributes in one table to another
- GeoNet: How to find missing records in Join
- FAQ: Why does the attribute table have Null or empty values after a join?