topic Add Join not keeping all target features in ArcGIS Pro Questions

Add Join not keeping all target features

YusefSamari — Wed, 25 Oct 2023 15:54:45 GMT

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?

Re: Add Join not keeping all target features

Robert_LeClair — Wed, 25 Oct 2023 17:14:57 GMT

First, is your data stored in a file or enterprise geodatabase?

Second - from the Help - "If the join results are unexpected or incomplete, review whether the Join Table Field and Input Join Field parameter values are indexed. If they're not, try deleting and re-creating the index and rerun the tool."

Third - you can run the Validate Join (Data Managemen) GP tool to see if the join is valid and determine cardinality.

Re: Add Join not keeping all target features

YusefSamari — Wed, 25 Oct 2023 21:07:20 GMT

Hi Robert, thanks for your replies.

The data are stored in a file geodatabase.

I have tried running this with the Join and Input Table Fields indexed and non-indexed. I get the same result either way.

This is the output from the Validate

I'm not sure what extra that tells me, as I already knew there were only 2 matches, but I want to retain all the rows that don't match.

Re: Add Join not keeping all target features

Robert_LeClair — Wed, 25 Oct 2023 21:14:17 GMT

Can you share the data with me so I may test on my side? If so, my email is rleclair at esri dot com - thx!

Re: Add Join not keeping all target features

avonmoos — Tue, 31 Oct 2023 23:01:33 GMT

I've ran into this issue in the past and the work around to show all of the joined data was to export the table to excel and all of the complete joined dataset will appear.

Re: Add Join not keeping all target features

ThomasHynes — Wed, 15 Nov 2023 16:35:55 GMT

This didn't work for me. Tried exporting join table to csv and xlsx

Re: Add Join not keeping all target features

ThomasHynes — Wed, 15 Nov 2023 16:55:38 GMT

I was having this issue joining a file geodatabase table to a SDE feature class. I was able to work around this by copying the feature class to a file geodatabase. After that, all target features did show successfully. I did call Support and they are escalating the issue to development

Re: Add Join not keeping all target features

LJackson29 — Wed, 24 Jan 2024 21:52:26 GMT

I have encountered this issue as well when joining a table to a layer both from file GDBs in Pro 3.2. The validate shows that there are 2 records that are not joining, but because not all the input records are being retained despite the check box being checked, I cannot see which two records out of 400,000+ are not in both layers.

After completing the join, you can see there are only 418,430 records in the join table

Please get this fixed ☹️

Re: Add Join not keeping all target features

AveryDurham1 — Wed, 07 Feb 2024 14:28:06 GMT

I had this same problem. It was solved for me by roving the definition query that I had on the join table.

Re: Add Join not keeping all target features

ChristineSeidel — Wed, 21 Feb 2024 20:29:07 GMT

Yep, had the same issue trying to join a Table to a feature class (both stored in the same file geodatabase). The feature class had a definition query and 'keep all input records' was not honored. So, I exported the filtered feature class to a Table. Then established the join from Table to Table (neither having a definition query) and then finally the 'keep all input records' when joining was a success! ...though quite a nuisance of a work around to generate extra tables just to find the misfits.

Re: Add Join not keeping all target features

Bud — Fri, 23 Feb 2024 16:51:27 GMT

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:

(view in My Videos)

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= 100

That 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.

Re: Add Join not keeping all target features

Bud — Tue, 14 May 2024 02:20:48 GMT

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.
1. 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

--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

Re: Add Join not keeping all target features

JoshuaBixby — Thu, 22 Feb 2024 16:32:47 GMT

Doing some basic checks/testing in Pro 3.2.2 with file geodatabase feature classes and tables, I see the expected behavior and not this behavior. Can you provide more information about the tables involved. Are they both in the same workspace? Is that a file geodatabase, mobile geodatabase, geopackage, etc...? If they are in different workspaces, what are those?

Also, what version of Pro are you using?

Re: Add Join not keeping all target features

Bud — Fri, 23 Feb 2024 16:55:38 GMT

Re: Add Join not keeping all target features

justin-mpls — Mon, 07 Apr 2025 15:36:09 GMT

Just wanted to share my experience with this problem as well. This is on ArcGIS Pro 3.1.3, attempting to join two feature classes within the same file geodatabase. I was trying to join a feature class of ~500 records to one with about 78,000 records and got only 88 matches when I should have got close to 78,000 matches. The key fields were indexed on both sides. First I tried eliminating all null values from the key fields in both tables and this made no difference. Then I checked using Python to make sure the target table key values were a superset of the join table keys (they were). Finally, I stumbled upon this thread (and this one https://community.esri.com/t5/arcgis-pro-questions/add-join-not-working/td-p/1142660) and deleted and rebuilt the attribute indexes for the join fields, which finally solved the problem.

@Robert_LeClair

The tool help says "If the join results are unexpected or incomplete, review whether the Join Table Field and Input Join Field parameter values are indexed. If they're not, try deleting and re-creating the index and rerun the tool." Because I had already indexed the fields, I didn't think this would be the problem. The help should be more clear that recreating an existing index may be necessary.

It would be very helpful to document this problem and solution on one of the knowledge base pages for join issues, such as this one:

https://support.esri.com/en-us/knowledge-base/problem-joining-two-fields-does-not-return-any-matching-000025381

Thanks.

Re: Add Join not keeping all target features

Robert_LeClair — Mon, 07 Apr 2025 20:54:55 GMT

With regard to documenting this problem and providing a solution, likely this would need to be created by Esri Support Services staff. I did a cursory search of existing Knowledge Base articles and did not find any with search terms of index create join - With regard to the tool help page item - if you go to the bottom right of the webpage, there is a button click - "Feedback on this topic" one can provide feedback to the help authors to investigate and improve.

Lastly and maybe related, there is BUG-000128476 - Creating a join does not work if the key field’s type is a short integer. Not sure if this is related to what the OP is experiencing.

Re: Add Join not keeping all target features

SH_DH — Fri, 19 Dec 2025 19:08:51 GMT

This solved my problem with this issue. Seems like a bug though.