data.table Joins and merges


A join combines two tables containing related columns. The term covers a wide range of operations, essentially everything except appending the two tables. "Merge" is a synonym. Type ?`[.data.table` for the official docs.


  • x[i, on, j]
    # join: data.table x & data.table or list i
  • x[!i, on, j]
    # anti-join


Working with keyed tables

If x & i have a key or x is keyed to match i's first few columns, then the on can be skipped like x[i].

Disambiguating column names in common

In j of x[i, on, j], columns of i can be referred with i.* prefixes.

Grouping on subsets

In j of x[i, on, j, by=.EACHI], j is computed for each row of i.

This is the only value of by worth using. For any other value, columns of i are not available.