arcpy Add Join with "KEEP_ALL" Does not Keep All

Question

Using arcpy for ArcGIS 10.5.1, I'm joining a feature layer to a table using arcpy.AddJoin_management() with the KEEP_ALL option in order to keep all features whether they have a related record in the joined table or not. However, after running the join, the only features in the layer are those that have a related record in the joined table.

My code is:

    arcpy.MakeFeatureLayer_management(inFeatures, tmpLayer)
    arcpy.MakeTableView_management(joinTable, tmpView, "Classification = '{}'".format(seasonClassification))
    arcpy.AddJoin_management(tmpLayer, inField, tmpView, joinField, join_type="KEEP_ALL")

I know I've had this working as expected in other scripts, but not in this case.

What am I doing wrong? How do I get it to keep all features after the join?

Answer 1

I've found that it works as expected when joining to a table, but not when joining to a table view (or at least, not a table view with a definition query - I haven't tested without the query).

So my work around is to use arcpy.TableToTable_conversion() to create a temporary in_memory table from the table view, and join to that instead of joining to the table view itself.

This code is now working fine for me:

arcpy.MakeFeatureLayer_management(inFeatures, tmpLayer)
arcpy.MakeTableView_management(joinTable, tmpView, "Classification = '{}'".format(seasonClassification))
#  Need to use a temporary table for the join, otherwise "KEEP_ALL" fails to keep all when joining to a view (at least in this case)
arcpy.TableToTable_conversion(tmpView, os.path.dirname(tmpTable), os.path.basename(tmpTable))
arcpy.Delete_management(tmpView)
arcpy.AddJoin_management(tmpLayer, inField, tmpTable, joinField, join_type="KEEP_ALL")

I'm not sure if this is a known limitation, or a bug. I've been unable to find any documentation for this behaviour.