Does it matter if a definition query on a join table is created before or after the join is created?

Question

ArcGIS Pro 3.1.2

I have tables A and B. I've joined from A to B; the relationship is 1:M.

I want to apply a definition query to table B. Are there any scenarios where it matters if I create the def query before creating the join vs. after creating the join?

Bud · Answer

Thanks @JonathanNeal.

JonathanNeal · Answer

Also, I added updated doc for these scenarios to Add Join, for 3.2+.  Please click the "Feedback on topic" at the bottom of the doc if it is not clear enough in the upcoming release. Add Join (Data Management)—ArcGIS Pro | Documentation

JonathanNeal · Answer

@Bud been working though the scenarios in 3.2, here is a summary of what I learned from testing.

Two likely scenarios come to mind in 3.1- and in ArcMap that you can mark as a YES:
1. Subqueries do not work if done before the join on datasources with ".".  There are some manual changes needed to fix them after the join.
2. Definition queries done after the join, remove join will not adjust the query.  This is by design at the moment because it is an easy manual task, but a difficult task to code.

In 3.2+ we have improved this.  Pro has the ability to hold multiple definition queries on one layer, while keeping just 1 as active.  Now we can keep the old definition query prior to the join in an inactive state so users can edit it if needed.  Also, fixes to MakeTableView to work with setting definition queries in arcpy make it easy to automate any adjustments.

So, in 3.2 to summarize the behavior:
1. subqueries and the "IN" operator are now working with the before scenario.
2. Remove Join will remove the definition query that was added before the join and set the before join definition query back to being active.  Any definition query created after the join will be unchanged, but likely invalid and need manual attention.