merge

merge(x, y, by = intersect(names(x), names(y)), by.x = by, by.y = by, all = FALSE, all.x = all, all.y = all, sort = TRUE, suffixes = c(".x", ".y"), no.dups = TRUE, incomparables = NULL, ...)

Returns data.frame· Added in v3.5.0· Updated June 17, 2026· Base Functions

rbasedata-framesjoinsmerge

What merge() does

merge() is base R’s workhorse for joining two data frames. You give it two frames and it produces a single frame whose rows are matched on one or more key columns. The result is always a data.frame with “automatic” row names (1, 2, 3, …).

merge is an S3 generic. The method you almost always hit is merge.data.frame. If you load data.table, that package registers merge.data.table and the call dispatches to it instead, which is usually what you want for large data. The reference below describes the data frame method.

Signature and arguments

The full signature, copied from the R 4.6.0 manual, is:

merge(x, y, by = intersect(names(x), names(y)),
      by.x = by, by.y = by,
      all = FALSE, all.x = all, all.y = all,
      sort = TRUE, suffixes = c(".x", ".y"),
      no.dups = TRUE, incomparables = NULL, ...)

The arguments you actually set in real code:

x, y — the two data frames to join. Order matters: columns of x come first in the result, then columns of y, after the shared by columns.
by — character, integer, or logical vector naming the key columns. Default is intersect(names(x), names(y)), which can surprise you (see the pitfalls section). The special name "row.names" (or position 0) means “join on row names”.
by.x, by.y — use when the key columns have different names in the two frames.
all, all.x, all.y — control which unmatched rows to keep. all = TRUE is a full outer join; all.x = TRUE is a left join; all.y = TRUE is a right join. The default of FALSE gives an inner join.
sort — sort the result on by columns. Default TRUE. Pass FALSE to keep input order (with no guarantee on ties).
suffixes — appended to non-by columns that share a name across the two frames. Default c(".x", ".y").
no.dups — TRUE by default since R 3.5.0. Also suffixes y’s version of a by column whose name clashes with a non-by column in x. Pre-3.5.0 the default was FALSE and you could end up with duplicate column names.
incomparables — values that cannot match. Pass incomparables = NA to make NA behave like SQL NULL (it cannot match another NA).

The return value is a data.frame with the shared by columns first, then the remaining columns of x, then the remaining columns of y. When you join on row names, an extra Row.names column is inserted at the left.

Joining on a common column

The simplest case: both frames have a column with the same name and you want to join on it. Here authors has surname and books has name, so you point the join at the right column in each frame with by.x and by.y.

authors <- data.frame(
  surname     = c("Tukey", "Venables", "Tierney", "Ripley", "McNeil"),
  nationality = c("US", "Australia", "US", "UK", "Australia"),
  deceased    = c("yes", "no", "no", "no", "no")
)
books <- data.frame(
  name         = c("Tukey", "Venables", "Tierney", "Ripley", "Ripley", "McNeil", "R Core"),
  title        = c("Exploratory Data Analysis", "Modern Applied Statistics...",
                   "LISP-STAT", "Spatial Statistics", "Stochastic Simulation",
                   "Interactive Data Analysis", "An Introduction to R"),
  other.author = c(NA, "Ripley", NA, NA, NA, NA, "Venables & Smith")
)

merge(authors, books, by.x = "surname", by.y = "name")
#    surname nationality deceased                         title other.author
# 1   McNeil    Australia       no Interactive Data Analysis         <NA>
# 2   Ripley          UK       no       Spatial Statistics         <NA>
# 3   Ripley          UK       no     Stochastic Simulation         <NA>
# 4  Tierney          US       no                  LISP-STAT         <NA>
# 5    Tukey          US      yes Exploratory Data Analysis         <NA>
# 6 Venables    Australia       no  Modern Applied Statistics...      Ripley

Notice that "R Core" from books has no match in authors and is dropped. This is an inner join: only keys that appear in both frames survive.

Joining on differently named columns

Real data rarely has matching key names. Use by.x and by.y to point at the right columns in each frame.

df1 <- data.frame(key_a = c("a", "b", "c"), val_x = 1:3)
df2 <- data.frame(key_b = c("a", "b", "d"), val_y = 4:6)

merge(df1, df2, by.x = "key_a", by.y = "key_b")
#   key_a val_x val_y
# 1     a     1     4
# 2     b     2     5

"c" from df1 and "d" from df2 have no match and are dropped.

Inner, left, right, and full outer joins

The all family of arguments maps cleanly onto SQL join types:

Setting	Behaviour	SQL equivalent
`all = FALSE` (default)	Keep only matched rows	`INNER JOIN`
`all.x = TRUE`	Keep all rows of `x`, fill `y` with `NA`	`LEFT JOIN`
`all.y = TRUE`	Keep all rows of `y`, fill `x` with `NA`	`RIGHT JOIN`
`all = TRUE`	Keep rows from both frames	`FULL OUTER JOIN`

A left join in action:

employees <- data.frame(id = c(1, 2, 3), name = c("Ada", "Lin", "Bo"))
salaries  <- data.frame(id = c(1, 2, 4), pay  = c(90000, 120000, 75000))

merge(employees, salaries, by = "id", all.x = TRUE)
#   id name   pay
# 1  1  Ada  90000
# 2  2  Lin 120000
# 3  3    Bo     NA

Bo has no salary row, so pay is NA. If you want Bo dropped (matched rows only), leave all.x unset.

For a full outer join that keeps both Bo and the unmatched id = 4 row from salaries:

merge(employees, salaries, by = "id", all = TRUE)
#   id name   pay
# 1  1  Ada  90000
# 2  2  Lin 120000
# 3  3   Bo     NA
# 4  4 <NA>  75000

Joining on row names

Pass by = 0 (equivalent to by = "row.names") to use row names as the key. The result gains a Row.names character column at the left.

df1 <- data.frame(score = c(80, 92, 77), row.names = c("alice", "bob", "carol"))
df2 <- data.frame(grade = c("B", "A", "C"),  row.names = c("alice", "bob", "dave"))

merge(df1, df2, by = 0)
#   Row.names score grade
# 1     alice    80     B
# 2       bob    92     A

carol and dave are dropped because they have no counterpart in the other frame. The extra Row.names column is the classic source of surprise here: it was not in either input.

Compound keys

Pass a vector to by to join on more than one column. The match is row-wise across all of them, so two rows match only when every key column agrees.

sales <- data.frame(
  region  = c("EU", "EU", "US", "US"),
  quarter = c("Q1", "Q2", "Q1", "Q2"),
  revenue = c(100, 120, 90, 110)
)
targets <- data.frame(
  region  = c("EU", "EU", "US"),
  quarter = c("Q1", "Q2", "Q1"),
  goal    = c(95, 115, 100)
)

merge(sales, targets, by = c("region", "quarter"))
#   region quarter revenue goal
# 1     EU      Q1     100   95
# 2     EU      Q2     120  115
# 3     US      Q1      90  100

The US Q2 row of sales is dropped because it has no targets counterpart.

Common pitfalls

The default by is the intersection of column names. If your frames share two column names (say id and date) and you only meant to join on id, merge silently joins on both. Always pass by = explicitly in production code, even when the intersection is what you want, because the silent version of the bug is hard to spot.

NA matches NA by default. This differs from SQL, where NULL never matches. To get SQL-like behaviour, pass incomparables = NA. With two frames whose key column contains NA, the row count drops sharply:

x <- data.frame(k1 = c(NA, NA, 3, 4, 5), data = 1:5)
y <- data.frame(k1 = c(NA, 2, NA, 4, 5), data = 1:5)

nrow(merge(x, y, by = "k1"))                      # 6  — NA pairs match
# [1] 6
nrow(merge(x, y, by = "k1", incomparables = NA))  # 2  — NA pairs skipped
# [1] 2

An empty by triggers a Cartesian product. merge(x, y, by = character(0)) returns a frame with nrow(x) * nrow(y) rows. This is an easy footgun when you build a key vector programmatically and forget the empty case.

When two non-by columns share a name, merge appends suffixes (default .x and .y). If the suffixed names are still not unique, you get an error rather than a silent collision. Pass a different suffixes to fix this.

For large tables, merge is not the right tool. Its complexity is roughly proportional to the size of the answer, but it copies the inputs and reorders them. data.table::merge.data.table and dplyr::*_join (for example dplyr::left_join) are typically an order of magnitude faster on real data and have a more predictable interface.

There is also a hard limit: inputs must have fewer than 2^31 rows. Long vectors are not supported here.