Add Join not keeping all target features
I am trying to join a table to a feature class using the Add Join tool. The box 'Keep All Target Features' is ticked:
Based on my previous experience, and the info on the pop-up box of that parameter, this should mean that all features in the input layer are kept, regardless of whether the join fields match. However, the resulting output is only including input features that match on the join table field, regardless of whether the box is ticked. Basically it seems stuck on performing an inner join even though an outer join is selected. This happens regardless of whether or not the fields are indexed. Both the feature class and the table are stored in a geodatabase.
Please can anyone advise as to why this might be happening?
ArcGIS Pro 3.2.2; File Geodatabase
To build on the comments about definition queries and joining:
When joining from table A to B, if B has a definition query prior to the join (such as TYPE=100), then the definition query will be applied to A (joined to B); I'll call it AB.
If B.TYPE has rows where TYPE is not 100, then the definition query will exclude those rows in AB.
Video:
That can be confusing because the user might have considered the definition query to only apply to B. But in the AB join, the definition query applies to the entire query.
If we look at the Diagnostic Monitor tool, we can see that ArcGIS Pro has sent this SQL query to the database (the SQL formatting is mine):
SELECT
OBJECTID,
Shape,
Shape_Length,
Shape_Area,
ID,
OBJECTID,
Shape_Length,
Shape_Area,
ID,
TYPE
FROM
A
LEFT OUTER JOIN
B
ON ID=ID
WHERE
TYPE= 100That WHERE clause applies to the entire query.
If we want to include all the rows from A, regardless if there is a match in B, and if we want to keep the definition query, then we'd need to add OR B.TYPE IS NULL to the definition query.
B.TYPE = 100 OR B.TYPE IS NULL
But I'm not an expert. Maybe @JonathanNeal, @JoshuaBixby, @MarceloMarques, or @AJR could weigh in or at least find this interesting.
There is a technical article or bug report somewhere about this topic that suggests the OR B.TYPE IS NULL workaround. But I can't find it right now.
I wonder if there are any other attribute filters, other than definition queries, such as time filters, range filters, or selection layers (selection definition set) that might have similar quirks? I haven't wrapped my head around that yet.
I'm starting to second-guess this comment. Testing ongoing.
When I think about this more, my definition query that has OR TYPE IS NULL is kind of useless. It doesn't do anything; it doesn't pare down the rows in B.
So maybe a different approach is needed. Either get rid of the definition query, or, if B does need to be pared down where TYPE=100, then maybe that should be done some other way, prior to the join.
Options for pre-filtering:
- Query Layer (enterprise geodatabase only)
- Database View
- Export (using a definition query or selection) to a static copy, as @ChristineSeidel suggested
- Make Query Table, since it doesn't put its SQL expression in a definition query, it hides the SQL expression, unlike Make Table View and Make Feature Layer.
- See the list of options in this post: Consistent/visible SQL expressions and query definitions in layers.
Here's the result when using Make Query Table to pre-filter B before joining. It seems to work:
The downside is that the SQL expression is hidden. As far as I can tell, it is only visible via code, such as saving the layer as a .LYRX and then viewing the JSON in a text editor. So, users would have no way of knowing that the table is being pared down. The best we can do is put something in the layer name, such as "B_QueryTable_Filtered".
Edit: It turns out there is an existing bug report that also suggests the Make Table workaround:
- BUG-000153808 The Add Join tool does not correctly honor the definition query on the join table.
- Workaround: “Use the Make Query Table tool to create the filter or export the filtered table.”
Or, do the join in a full-blown SQL query (database view or query layer) instead of doing the join in Pro via Add Join. The downside is that it's not possible to edit the data in a database view or query layer.
The SQL query might look like this:
select
a.id,
b.id,
b.type
from
a
left join
(select * from b where type=100) b
on a.id=b.id 
or
--tested in Oracle 18c
select a.id, b.id, b.type from a left join b on a.id=b.id and type=100
There are likely other ways to do it in SQL, such as using the WITH clause (aka common table expression) in enterprise and mobile geodatabases to pre-filter B.
Idea: Add Join — Indicate that a definition query on join table will effectively result in an inner join
