minijoin

The purpose of minijoin is to provide a zero-dependency slice of dplyr-style syntax for mutating joins, all built atop base R’s merge(). The README provides the full argument reference; this page compares all four join functions side by side on two small toy data frames with a partial overlap, then covers the by argument in more detail.

Functions

The minijoin.R script supplies four core functions:

• .join_inner_join(x, y, by, ...) is used to keep only rows with a match in both x and y.
• .join_left_join(x, y, by, ...) is used to keep all rows of x, filling in NA for y’s columns where there’s no match.
• .join_right_join(x, y, by, ...) is used to keep all rows of y, filling in NA for x’s columns where there’s no match.
• .join_full_join(x, y, by, ...) is used to keep all rows of both x and y, filling in NA on whichever side has no match.

There is also one internal helper:

• .join_worker() is the shared implementation that all four join functions delegate to, after fixing the right combination of merge()’s all.x/all.y arguments; it also restores x’s original row order, which merge() doesn’t guarantee on its own.

Minimal data frames

bands <- data.frame(
  band = c("Beatles", "Rolling Stones", "Kinks"),
  founded = c(1960, 1962, 1963)
)
albums <- data.frame(
  band = c("Beatles", "Kinks", "Queen"),
  album = c("Abbey Road", "Arthur", "A Night at the Opera")
)
bands

            band founded
1        Beatles    1960
2 Rolling Stones    1962
3          Kinks    1963

albums

     band                album
1 Beatles           Abbey Road
2   Kinks               Arthur
3   Queen A Night at the Opera

Here we have bands and albums as the data sets, sharing "Beatles" and "Kinks". The "Rolling Stones" exist only in bands, but "Queen" exists only in albums. The four join types differ only in which of those non-matching rows they keep.

Inner join: only rows matching in both

Neither "Rolling Stones" nor "Queen" appears – an inner join is the most restrictive of the four, keeping only rows with a counterpart on the other side:

.join_inner_join(bands, albums, by = "band")

     band founded      album
1 Beatles    1960 Abbey Road
2   Kinks    1963     Arthur

Left join: all rows of x (bands)

The "Rolling Stones" row is kept, with NA for album, since it has no match in albums. This is the join to reach for when x is your “main” table and you’re enriching it with optional data from y:

.join_left_join(bands, albums, by = "band")

            band founded      album
1        Beatles    1960 Abbey Road
2 Rolling Stones    1962       <NA>
3          Kinks    1963     Arthur

Right join: all rows of y (albums)

The "Queen" row is kept, with NA for founded. A right join is really just a left join with the arguments swapped – .join_right_join(x, y, ...) behaves like .join_left_join(y, x, ...), with columns in x‘s original order. Note the row order below: matched rows still come first, in bands’ original order, with the unmatched-in-x row ("Queen") appended at the end – minijoin always restores x’s original row order for matched rows, rather than y’s:

.join_right_join(bands, albums, by = "band")

     band founded                album
1 Beatles    1960           Abbey Road
2   Kinks    1963               Arthur
3   Queen      NA A Night at the Opera

Full join: all rows of both

Both "Rolling Stones" and "Queen" are kept, each with an NA in the column they don’t have a match for – a full join never drops a row from either side, so it’s the join to reach for when you want to see everything and let missing matches show up as NA rather than disappearing silently:

.join_full_join(bands, albums, by = "band")

            band founded                album
1        Beatles    1960           Abbey Road
2 Rolling Stones    1962                 <NA>
3          Kinks    1963               Arthur
4          Queen      NA A Night at the Opera

by is always explicit

Unlike dplyr, there’s no auto-detection of shared column names – by must always be supplied. This is a deliberate simplification, not an oversight, to avoid a class of silent bugs from an unintended shared column name (two data frames that happen to both have an id column with unrelated meanings, for instance).

When the join column has different names on each side, by takes a named character vector instead of a single string, c("x_name" = "y_name") – mirroring dplyr’s own syntax for the same situation:

albums_renamed <- albums
names(albums_renamed)[1] <- "artist"
albums_renamed

   artist                album
1 Beatles           Abbey Road
2   Kinks               Arthur
3   Queen A Night at the Opera

.join_inner_join(bands, albums_renamed, by = c("band" = "artist"))

     band founded      album
1 Beatles    1960 Abbey Road
2   Kinks    1963     Arthur

Note that the output keeps x’s column name (band), not y’s (artist) – the join collapses the two into a single column, using whichever name x had.