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

[Bud]

ArcGIS Pro 3.2.2; File Geodatabase

When joining to a table that has a definition query, the definition query gets applied to the input table (and to the join table). The definition query effectively becomes an inner join, even if "Keep all input records" was checked in the join.

That ends up being a gotcha in a lot of cases.

Users would expect the definition query to be applied as a "pre-filter" to the join table only, instead of applying the definition query to the resulting joined tables.

Could that behaviour be changed so that the definition query on the join table remains independent (as a pre-filter)?

Maybe something like this:

Related:

• Add Join not keeping all target features
• Add Join — Indicate that a definition query on join table will effectively result in an inner join

[Bud]

• 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 defini...
• 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 ...
• BUG-000164109 Exported output is not the same when involving definition queries and join processes.

[Bud]

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 

[Bud]

As a workaround:

We can try hiding/embedding the SQL expression using Make Query Table. The SQL expression behaves like a pre-filter, applied before the join is performed, which is what we want, unlike definition queries. https://community.esri.com/t5/arcgis-pro-ideas/consistent-visible-sql-expressions-and-query/idc-p/13... But we have to be careful not to forget about that SQL expression because it isn't visible or modifiable anywhere. I find it helps to describe the SQL expression in the layer's name.