Add Join — Indicate that a definition query on join table will effectively result in an inner join

Bud — Sat, 24 Feb 2024 16:04:52 GMT

ArcGIS Pro 3.2.2

Background: Add Join not keeping all target features

When joining from tables A to B, if B has a definition query such as TYPE=100, then that definition query will effectively result in an inner join in the resulting AB join.

The Add Join tool says:

The join table has a definition query which will be applied to the input after performing the join.
Add Join should warn user if join table has a definition query

That's helpful. But I wonder if it would be even more helpful to say that the definition query will effectively result in a matches-only join, even if "Keep all input records" is checked.

Result:

Users are often confused when a join table definition query excludes rows from the resulting join. Or worse, the user doesn't notice and produces incorrect results.

It's a common issue. So I'm wondering if anything more can be done to prevent it.

Data Management Ideas - Honor Definition Query When Joining Feature Classes
ArcGIS Pro Ideas (Closed) - Honor Definition Query When Joining Feature Classes
Bug - Article ID:000007166 - Open Attribute Table returns no values on a geodatabase point feature class with a join and a definition query based on a joined field
BUG-000153808 The Add Join tool does not correctly honor the definition query on the join table.
BUG-000131999 Definition query not working as expected in a joined feature layer.
BUG-000129607 Add Join does not correctly honor definition query on join table.
BUG-000153950 In ArcGIS Pro, when adding a join, the Keep All Target Features option is not honored if the joined table has a definition query applied to it.
BUG-000164109 Exported output is not the same when involving definition queries and join processes.

Re: Add Join — Indicate that a definition query on join table will effectively result in an inner join

Bud — Sat, 10 Aug 2024 14:23:32 GMT

Idea - Add Join — Create independent definition query on join table (pre-filter)

Re: Add Join — Indicate that a definition query on join table will effectively result in an inner join

Bud — Wed, 28 Aug 2024 05:53:21 GMT

If the join table has a definition query, the Keep all input records parameter will have no effect. Manually updating the definition query by appending or OBJECTID is null can fix this if appropriate.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm

idea Add Join — Indicate that a definition query on join table will effectively result in an inner join in ArcGIS Pro Ideas

Add Join — Indicate that a definition query on join table will effectively result in an inner join

Re: Add Join — Indicate that a definition query on join table will effectively result in an inner join

Re: Add Join — Indicate that a definition query on join table will effectively result in an inner join