Shape · Combine tables

Join

Matches rows from two tables on one or more key columns: inner, left, right, full, anti or semi. Keys can also compare with <, > or between, for range joins.

Also known as: Merge, merge queries

How it works

Wire the two tables into left and right, choose a Join type and click Add key under Match keys for each condition: a left key, an operator (=, <, ≤, >, ≥ or between, which takes a lower and an upper right column) and a right key. Inner, Left, Right and Full keep columns from both sides; when every key is an = on the same name, that column appears once. Left anti keeps left rows with no match, Right anti right rows with no match, Semi left rows with a match, each with that side's columns only. Match empty keys lets null match null on an = key; otherwise null never matches. Right-column prefix keeps every column of both sides and prefixes a right column whose name the left also has; Drop columns and Rename columns then apply to the output, and a name that finds nothing is skipped.

Inputs and outputs

Settings

{
  joinType: "inner"|"left"|"right"|"outer"|"anti"|"rightAnti"|"semi"
  keys: [{ leftKey, op: "="|"<"|"<="|">"|">="|"between", rightKey, rightHigh? }]
  nullsMatch?: boolean
  rightPrefix?: string
  dropColumns?: string[]
  renameColumns?: [{ from, to }]
}

Example

Each order should get the price band its quantity falls in. Join orders to bands with Inner and one key: qty between min_qty and max_qty. For customers who never ordered, join customers to orders with Left anti on customer_id = customer_id.

More in Combine tables

• Lookup — Adds columns from a reference table to every row by matching one key, like Excel's VLOOKUP. Rows without a match stay, with blanks.
• Union — Stacks the rows of two or more tables one under another. Columns line up by name or by position.
• Zip — Puts two tables side by side, row 1 with row 1, row 2 with row 2, with no key needed. Both tables must have the same number of rows.
• Cross Join — Pairs every row of one table with every row of the other, so 10 × 5 rows gives 50. For example, every store with every month.
• As-of Join — Matches each row to the closest row in another table by a sorted key such as a time. For example, attach the price in effect at the time of each trade.
• Fuzzy Match — Joins two tables on keys that are similar but not identical, such as customer names with typos. A score column shows how close each match is.