Join operations
Operations that bring two or more tables together. Among them are joining tables on a shared key, on a range or the nearest time, on approximate matches or by location, appending one table beneath another, keeping the rows found in another table, or comparing two tables to report the rows changed, added and removed. Some work out which records describe the same thing, and one collapses each group of matched records into one surviving record.
15 operations, grouped by the shape of the result, each with its status in arcform today.
One row out per row in, with columns added or changed
Group matched recordsRuns as a SQL step
Turn matched pairs into one group id per record, following matches transitively
A new grain: each row stands for a group of input rows
Pick a surviving recordRuns as a SQL step
Collapse each group of matched records into one surviving record by per-field rules
One table from two or more
Cross join tablesRuns as a SQL step
Cross join, cartesian product
Compare two tablesRuns as a SQL step
Compare two tables, report changed, added and removed rows (2 variants with a page)
Join on approximate matchesRuns as a SQL step
Approximate string match join (1 variant with a page)
Join tables on a keyRuns as a SQL step
Inner, left, right, full join on equal keys (4 variants with a page)
Look up values from a reference tableRuns as a SQL step
Enrich from a reference table (1 variant with a page)
Merge into a tableRuns as a SQL step
Merge into a target, upsert, slowly changing dimension (6 variants with a page)
Put tables side by sideRuns as a SQL step
Put tables side by side, row by row
Join on a range or nearest timeRuns as a SQL step
Inequality, interval, as-of, nearest-in-time join (1 variant with a page)
Resolve entitiesNot built yet
Entity resolution with a trained or probabilistic matching model; reconciliation against an entity service
Keep rows found in another tableRuns as a SQL step
Keep or drop rows by existence in another table (2 variants with a page)
Intersect or subtract tablesRuns as a SQL step
Intersect, except (2 variants with a page)
Join by locationRuns as a SQL step
Join on spatial relationship, distance, containment (1 variant with a page)
Append tablesRuns as a SQL step
Append tables by rows (3 variants with a page)