idea Add Join — Create independent definition query on join table (pre-filter) in ArcGIS Pro Ideas

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

Bud — Fri, 23 Feb 2024 16:47:14 GMT

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:

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

Bud — Fri, 01 Mar 2024 16:11:58 GMT

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.

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

Bud — Wed, 28 Aug 2024 05:56:38 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

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

Bud — Wed, 28 Aug 2024 05:59:05 GMT

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.