Select to view content in your preferred language

Add Join not keeping all target features

1898
13
10-25-2023 08:54 AM
YusefSamari
Occasional Contributor

I am trying to join a table to a feature class using the Add Join tool. The box 'Keep All Target Features' is ticked:

YusefSamari_0-1698249012470.png

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?

13 Replies
Bud
by
Honored Contributor

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

That WHERE clause applies to the entire query.

Bud_0-1708576088023.png

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.

0 Kudos
Bud
by
Honored Contributor

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:

  1. Query Layer (enterprise geodatabase only)
  2. Database View
  3. Export (using a definition query or selection) to a static copy, as @ChristineSeidel suggested
  4. 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:

Bud_3-1708612431217.png

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: 

 

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 

Bud_2-1708580097133.png

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

Bud_3-1708580110449.png


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

0 Kudos
Bud
by
Honored Contributor
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos