Clarifying the differences between Merge, Append, and Join tools in ArcGIS Pro

Question

Hey all,

I just want to make sure that I have my summary of tools in ArcGIS Pro correct.

Merge: Takes two or more layers of the same type (e.g., points, lines, or polygons) and combines them into a new layer. The input layers do not need to have matching schemas (field names, data types, and order).

Append: Takes one or more layers of the same type and adds their features to an existing target layer. The input layers and the target layer must have matching schemas. *There is also an "upsert" option within this tool that allows you to update fields of data on the target layer with info from the new layer if there is a shared unique identifier, matching one-to-one.

Join: There are several join options available, with Spatial Join not requiring matching schemas between the input layers. However, for most of the other join options (about 5 or so), there must be a matching unique identifier between the layers. These join operations typically create a new layer or a temporary in-memory join, with the exception of the "Add Join" command. When performing a join, you are effectively adding new data fields/columns to a layer, expanding the table horizontally to the right, rather than appending new data to the bottom of the table.

Is this a good overall summary?

JonJones1 · Answer

What is the best option if I have two layers, one for fire hydrant tests in 2023 and the other for tests in 2024? The schemes for the 2023 year include "2023 pressure test" and "2023 water quality test," and for the 2024 year, they include "2024 pressure test" and "2024 water quality test." I want a single layer that consolidates all these fields, so for each fire hydrant, when I view its attributes, I see "2023 pressure test," "2023 water quality test," "2024 pressure test," and "2024 water quality test."

Please note, some new fire hydrants were added so that some will only have results for 2024, and it's okay if the 2023 results are all empty.

clt_cabq · Answer

I think you have it mostly a few subtleties  to keep in mind:

* Merge creates a new feature class, that incorporates ALL fields from both/all inputs so your new output table can become unwieldy very quickly if you have dissimilar schemas among your inputs and don't use the field mapping tools. Merge results can also get funky if you have overlapping polygons or line segments.
* Append essentially updates an existing feature class, and you have to handle the field mapping if you don't have perfectly synonymous schema in your inputs.
* For joins, its important to understand that the result is not permanent, you're not altering the schema of the input target table, and you can't make changes to the joined fields. Joins also only work for tables where there is a 1:1 relationship between target and source. You might want to make sure you understand the difference between inner and left joins.
* Following on from this, you may want to make sure you understand how relates work as they are similar to joins but support 1:M relationships.