Unable to get all records to join although the values in the two join fields match

Description

When joining tables with the option to keep all records, if all values in a field in one table match with all values in a field of another table, all records are included in the joined table. However, in some cases, when joining such tables, only some records are included in the joined table.

Cause

This may caused by preceding spaces or trailing spaces in the values of one or both of the join fields.

Solution or Workaround

Remove the preceding and trailing spaces from the values in the relevant fields before joining the fields.

1. In the attribute table of the feature, right-click the header of the field that is used for joining, and click Field Calculator.
2. Use the following Python expression on the relevant join field to remove all the trailing spaces.

!fieldname!.rstrip()

Note:
Change fieldname to the user's respective field name.

3. Use the following Python expression to remove any preceding spaces from the values in the join field.

!fieldname!.lstrip()

Note:
Apply these steps on both join inputs to ensure that preceding or trailing spaces are not hindering a proper join, and proceed with the join operation.