By Dan Goldstein

2009-08-19 13:18:11 8 Comments

Given two data frames:

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

#  CustomerId Product
#           1 Toaster
#           2 Toaster
#           3 Toaster
#           4   Radio
#           5   Radio
#           6   Radio

#  CustomerId   State
#           2 Alabama
#           4 Alabama
#           6    Ohio

How can I do database style, i.e., sql style, joins? That is, how do I get:

  • An inner join of df1 and df2:
    Return only the rows in which the left table have matching keys in the right table.
  • An outer join of df1 and df2:
    Returns all rows from both tables, join records from the left which have matching keys in the right table.
  • A left outer join (or simply left join) of df1 and df2
    Return all rows from the left table, and any rows with matching keys from the right table.
  • A right outer join of df1 and df2
    Return all rows from the right table, and any rows with matching keys from the left table.

Extra credit:

How can I do a SQL style select statement?


@jangorecki 2015-12-11 09:23:09

Update on data.table methods for joining datasets. See below examples for each type of join. There are two methods, one from [.data.table when passing second data.table as the first argument to subset, another way is to use merge function which dispatches to fast data.table method.

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join


dt1 =
dt2 =
setkey(dt1, CustomerId)
setkey(dt2, CustomerId)
# right outer join keyed data.tables

setkey(dt1, NULL)
setkey(dt2, NULL)
# right outer join unkeyed data.tables - use `on` argument
dt1[dt2, on = "CustomerId"]

# left outer join - swap dt1 with dt2
dt2[dt1, on = "CustomerId"]

# inner join - use `nomatch` argument
dt1[dt2, nomatch=NULL, on = "CustomerId"]

# anti join - use `!` operator
dt1[!dt2, on = "CustomerId"]

# inner join - using merge method
merge(dt1, dt2, by = "CustomerId")

# full outer join
merge(dt1, dt2, by = "CustomerId", all = TRUE)

# see ? arguments for other cases

Below benchmark tests base R, sqldf, dplyr and data.table.
Benchmark tests unkeyed/unindexed datasets. Benchmark is performed on 50M-1 rows datasets, there are 50M-2 common values on join column so each scenario (inner, left, right, full) can be tested and join is still not trivial to perform. It is type of join which well stress join algorithms. Timings are as of sqldf:0.4.11, dplyr:0.7.8, data.table:1.12.0.

# inner
Unit: seconds
   expr       min        lq      mean    median        uq       max neval
   base 111.66266 111.66266 111.66266 111.66266 111.66266 111.66266     1
  sqldf 624.88388 624.88388 624.88388 624.88388 624.88388 624.88388     1
  dplyr  51.91233  51.91233  51.91233  51.91233  51.91233  51.91233     1
     DT  10.40552  10.40552  10.40552  10.40552  10.40552  10.40552     1
# left
Unit: seconds
   expr        min         lq       mean     median         uq        max 
   base 142.782030 142.782030 142.782030 142.782030 142.782030 142.782030     
  sqldf 613.917109 613.917109 613.917109 613.917109 613.917109 613.917109     
  dplyr  49.711912  49.711912  49.711912  49.711912  49.711912  49.711912     
     DT   9.674348   9.674348   9.674348   9.674348   9.674348   9.674348       
# right
Unit: seconds
   expr        min         lq       mean     median         uq        max
   base 122.366301 122.366301 122.366301 122.366301 122.366301 122.366301     
  sqldf 611.119157 611.119157 611.119157 611.119157 611.119157 611.119157     
  dplyr  50.384841  50.384841  50.384841  50.384841  50.384841  50.384841     
     DT   9.899145   9.899145   9.899145   9.899145   9.899145   9.899145     
# full
Unit: seconds
  expr       min        lq      mean    median        uq       max neval
  base 141.79464 141.79464 141.79464 141.79464 141.79464 141.79464     1
 dplyr  94.66436  94.66436  94.66436  94.66436  94.66436  94.66436     1
    DT  21.62573  21.62573  21.62573  21.62573  21.62573  21.62573     1

Be aware there are other types of joins you can perform using data.table:
- update on join - if you want to lookup values from another table to your main table
- aggregate on join - if you want to aggregate on key you are joining you do not have to materialize all join results
- overlapping join - if you want to merge by ranges
- rolling join - if you want merge to be able to match to values from preceeding/following rows by rolling them forward or backward
- non-equi join - if your join condition is non-equal

Code to reproduce:

sapply(c("sqldf","dplyr","data.table"), packageVersion, simplify=FALSE)

n = 5e7
df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L))
df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L))
dt1 =
dt2 =

mb = list()
# inner join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x"),
               sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"),
               dplyr = inner_join(df1, df2, by = "x"),
               DT = dt1[dt2, nomatch=NULL, on = "x"]) -> mb$inner

# left outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all.x = TRUE),
               sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"),
               dplyr = left_join(df1, df2, by = c("x"="x")),
               DT = dt2[dt1, on = "x"]) -> mb$left

# right outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all.y = TRUE),
               sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"),
               dplyr = right_join(df1, df2, by = "x"),
               DT = dt1[dt2, on = "x"]) -> mb$right

# full outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all = TRUE),
               dplyr = full_join(df1, df2, by = "x"),
               DT = merge(dt1, dt2, by = "x", all = TRUE)) -> mb$full

lapply(mb, print) -> nul

@SymbolixAU 2016-05-10 21:52:09

Is it worth adding an example showing how to use different column names in the on = too?

@jangorecki 2016-05-10 22:00:14

@Symbolix we may wait for 1.9.8 release as it will add non-equi joins operators to on arg

@SymbolixAU 2016-08-16 03:47:08

Another thought; is it worth adding a note that with there is the default sort = TRUE argument, which adds a key during the merge and leaves it there in the result. This is something to watch out for, especially if you're trying to avoid setting keys.

@statquant 2016-11-07 09:44:32

I am surprised noone mentioned that most of those are not working if there are dups...

@David Arenburg 2017-06-11 07:51:09

@statquant You can do a Cartesian join with data.table, what do you mean? Can you be more specific please.

@Matt Parker 2009-08-19 15:15:41

By using the merge function and its optional parameters:

Inner join: merge(df1, df2) will work for these examples because R automatically joins the frames by common variable names, but you would most likely want to specify merge(df1, df2, by = "CustomerId") to make sure that you were matching on only the fields you desired. You can also use the by.x and by.y parameters if the matching variables have different names in the different data frames.

Outer join: merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

Left outer: merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

Right outer: merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

Cross join: merge(x = df1, y = df2, by = NULL)

Just as with the inner join, you would probably want to explicitly pass "CustomerId" to R as the matching variable. I think it's almost always best to explicitly state the identifiers on which you want to merge; it's safer if the input data.frames change unexpectedly and easier to read later on.

You can merge on multiple columns by giving by a vector, e.g., by = c("CustomerId", "OrderId").

If the column names to merge on are not the same, you can specify, e.g., by.x = "CustomerId_in_df1", by.y = "CustomerId_in_df2" where CustomerId_in_df1 is the name of the column in the first data frame and CustomerId_in_df2 is the name of the column in the second data frame. (These can also be vectors if you need to merge on multiple columns.)

@Nicholas Hamilton 2013-02-12 04:42:21

@MattParker I have been using sqldf package for a whole host of complex queries against dataframes, really needed it to do a self-cross join (ie data.frame cross-joining itself) I wonder how it compares from a performance perspective....???

@Matt Parker 2013-02-12 16:22:41

@ADP I've never really used sqldf, so I'm not sure about speed. If performance is a major issue for you, you should also look into the data.table package - that's a whole new set of join syntax, but it's radically faster than anything we're talking about here.

@Manoj Kumar 2016-04-07 20:08:51

With more clarity and explanation.....…

@Dileep Kumar Patchigolla 2016-12-01 10:40:12

A minor addition that was helpful for me - When you want to merge using more than one column: merge(x=df1,y=df2, by.x=c("x_col1","x_col2"), by.y=c("y_col1","y_col2"))

@marbel 2016-12-02 19:44:35

This works in data.table now, same function just faster.

@Andy 2018-02-27 18:55:08

Wouldn't an outer join be: FullJoin<-merge(x = df1, y = df2, by = "CustomerId", all = TRUE) OuterJoin<-FullJoin[which(apply(FullJoin,1,function(x) any(,]

@ifly6 2018-03-15 19:30:21

Is merge in place? Or does it have to be assigned?

@Frank 2018-09-04 16:30:15

Update join. One other important SQL-style join is an "update join" where columns in one table are updated (or created) using another table.

Modifying the OP's example tables...

sales = data.frame(
  CustomerId = c(1, 1, 1, 3, 4, 6), 
  Year = 2000:2005,
  Product = c(rep("Toaster", 3), rep("Radio", 3))
cust = data.frame(
  CustomerId = c(1, 1, 4, 6), 
  Year = c(2001L, 2002L, 2002L, 2002L),
  State =[1:4]

# CustomerId Year Product
#          1 2000 Toaster
#          1 2001 Toaster
#          1 2002 Toaster
#          3 2003   Radio
#          4 2004   Radio
#          6 2005   Radio

# CustomerId Year    State
#          1 2001  Alabama
#          1 2002   Alaska
#          4 2002  Arizona
#          6 2002 Arkansas

Suppose we want to add the customer's state from cust to the purchases table, sales, ignoring the year column. With base R, we can identify matching rows and then copy values over:

sales$State <- cust$State[ match(sales$CustomerId, cust$CustomerId) ]

# CustomerId Year Product    State
#          1 2000 Toaster  Alabama
#          1 2001 Toaster  Alabama
#          1 2002 Toaster  Alabama
#          3 2003   Radio     <NA>
#          4 2004   Radio  Arizona
#          6 2005   Radio Arkansas

# cleanup for the next example
sales$State <- NULL

As can be seen here, match selects the first matching row from the customer table.

Update join with multiple columns. The approach above works well when we are joining on only a single column and are satisfied with the first match. Suppose we want the year of measurement in the customer table to match the year of sale.

As @bgoldst's answer mentions, match with interaction might be an option for this case. More straightforwardly, one could use data.table:

setDT(sales); setDT(cust)

sales[, State := cust[sales, on=.(CustomerId, Year), x.State]]

#    CustomerId Year Product   State
# 1:          1 2000 Toaster    <NA>
# 2:          1 2001 Toaster Alabama
# 3:          1 2002 Toaster  Alaska
# 4:          3 2003   Radio    <NA>
# 5:          4 2004   Radio    <NA>
# 6:          6 2005   Radio    <NA>

# cleanup for next example
sales[, State := NULL]

Rolling update join. Alternately, we may want to take the last state the customer was found in:

sales[, State := cust[sales, on=.(CustomerId, Year), roll=TRUE, x.State]]

#    CustomerId Year Product    State
# 1:          1 2000 Toaster     <NA>
# 2:          1 2001 Toaster  Alabama
# 3:          1 2002 Toaster   Alaska
# 4:          3 2003   Radio     <NA>
# 5:          4 2004   Radio  Arizona
# 6:          6 2005   Radio Arkansas

The three examples above all focus on creating/adding a new column. See the related R FAQ for an example of updating/modifying an existing column.

@smci 2014-04-13 10:39:03

dplyr since 0.4 implemented all those joins including outer_join, but it was worth noting that for the first few releases it used not to offer outer_join, and as a result there was a lot of really bad hacky workaround user code floating around for quite a while (you can still find this in SO and Kaggle answers from that period).

Join-related release highlights:

v0.5 (6/2016)

  • Handling for POSIXct type, timezones, duplicates, different factor levels. Better errors and warnings.
  • New suffix argument to control what suffix duplicated variable names receive (#1296)

v0.4.0 (1/2015)

  • Implement right join and outer join (#96)
  • Mutating joins, which add new variables to one table from matching rows in another. Filtering joins, which filter observations from one table based on whether or not they match an observation in the other table.

v0.3 (10/2014)

  • Can now left_join by different variables in each table: df1 %>% left_join(df2, c("var1" = "var2"))

v0.2 (5/2014)

  • *_join() no longer reorders column names (#324)

v0.1.3 (4/2014)

Workarounds per hadley's comments in that issue:

  • right_join(x,y) is the same as left_join(y,x) in terms of the rows, just the columns will be different orders. Easily worked around with select(new_column_order)
  • outer_join is basically union(left_join(x, y), right_join(x, y)) - i.e. preserve all rows in both data frames.

@Gregor 2016-12-22 18:30:04

This answer should either be updated or deleted. full_join and right_join have been part of dplyr for almost 2 years and separate x and y column names for even longer. Maj and Andrew Barr's answers seem to cover the dplyr methods well...

@smci 2016-12-23 13:33:35

@Gregor: no it shouldn't be deleted. It is important for R users to know that join capabilities were missing for many years, since most of the code out there contains workarounds or ad-hoc manual implementations, or ad-hocery with vectors of indices, or worse still avoids using these packages or operations at all. Every week I see such questions on SO. We'll be undoing the confusion for many years to come.

@smci 2016-12-23 13:55:36

Yes it should be updated at some point, but things change so often they're a moving target, so it'll happen whenever I get around to it.

@smci 2018-05-31 11:18:33

@Gregor and others who asked: updated, summarizing historical changes and what was missing for several years around when this question was asked. This illustrates why code from that period was majorly hacky, or avoided using dplyr joins and fell back on merge. If you check historical codebases on SO and Kaggle you can still see the adoption delay and the seriously confused user code this resulted in. Let me know if you still find this answer lacking.

@smci 2018-05-31 12:29:54

@Gregor: Those of us who adopted it mid-2014 didn't pick the best moment. (I thought there were earlier (0.0.x) releases around in 2013, but no, my mistake.) Regardless, there was still lots of crap code well into 2015, that was what motivated me to post this, I was trying to demystify the crud I found on Kaggle, github, SO.

@Gregor 2018-05-31 14:02:24

Yes, I understand, and I think you do a good job of that. (I was an early adopter too, and while I still like the dplyr syntax, the change from lazyeval to rlang backends broke a bunch of code for me, which drove me to learn more data.table, and now I mostly use data.table.)

@sanjeeb 2015-08-26 09:57:46

  1. Using merge function we can select the variable of left table or right table, same way like we all familiar with select statement in SQL (EX : Select a.* ...or Select b.* from .....)
  2. We have to add extra code which will subset from the newly joined table .

    • SQL :- select a.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId

    • R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df1)]

Same way

  • SQL :- select b.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId

  • R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df2)]

@Jaap 2017-09-11 11:35:31

For an inner join on all columns, you could also use fintersect from the data.table-package or intersect from the dplyr-package as an alternative to merge without specifying the by-columns. this will give the rows that are equal between two dataframes:

merge(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  3
dplyr::intersect(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  3
data.table::fintersect(setDT(df1), setDT(df2))
#    V1 V2
# 1:  B  2
# 2:  C  3

Example data:

df1 <- data.frame(V1 = LETTERS[1:4], V2 = 1:4)
df2 <- data.frame(V1 = LETTERS[2:3], V2 = 2:3)

@Etienne Low-Décarie 2012-03-11 06:24:15

There is the data.table approach for an inner join, which is very time and memory efficient (and necessary for some larger data.frames):


dt1 <- data.table(df1, key = "CustomerId") 
dt2 <- data.table(df2, key = "CustomerId")

joined.dt1.dt.2 <- dt1[dt2]

merge also works on data.tables (as it is generic and calls

merge(dt1, dt2)

data.table documented on stackoverflow:
How to do a data.table merge operation
Translating SQL joins on foreign keys to R data.table syntax
Efficient alternatives to merge for larger data.frames R
How to do a basic left outer join with data.table in R?

Yet another option is the join function found in the plyr package


join(df1, df2,
     type = "inner")

#   CustomerId Product   State
# 1          2 Toaster Alabama
# 2          4   Radio Alabama
# 3          6   Radio    Ohio

Options for type: inner, left, right, full.

From ?join: Unlike merge, [join] preserves the order of x no matter what join type is used.

@Beasterfield 2013-05-30 11:28:17

+1 for mentioning plyr::join. Microbenchmarking indicates, that it performs about 3 times faster than merge.

@marbel 2014-01-02 02:36:42

However, data.table is much faster than both. There is also great support in SO, i don't see many package writers answering questions here as often as the data.table writer or contributors.

@Aleksandr Blekh 2014-08-06 03:45:45

What is the data.table syntax for merging a list of data frames?

@R Yoda 2015-11-11 07:24:04

Please note: dt1[dt2] is a right outer join (not a "pure" inner join) so that ALL rows from dt2 will be part of the result even if there is no matching row in dt1. Impact: You result has potentially unwanted rows if you have key values in dt2 that do not match the dt1's key values.

@David Arenburg 2015-11-11 21:20:17

@RYoda you can just specify nomatch = 0L in that case.

@Andrew Barr 2014-02-06 21:35:06

You can do joins as well using Hadley Wickham's awesome dplyr package.


#make sure that CustomerId cols are both type numeric
#they ARE not using the provided code in question and dplyr will complain
df1$CustomerId <- as.numeric(df1$CustomerId)
df2$CustomerId <- as.numeric(df2$CustomerId)

Mutating joins: add columns to df1 using matches in df2

inner_join(df1, df2)

#left outer
left_join(df1, df2)

#right outer
right_join(df1, df2)

#alternate right outer
left_join(df2, df1)

#full join
full_join(df1, df2)

Filtering joins: filter out rows in df1, don't modify columns

semi_join(df1, df2) #keep only observations in df1 that match in df2.
anti_join(df1, df2) #drops all observations in df1 that match in df2.

@Aleksandr Blekh 2014-10-11 03:37:00

Why do you need to convert CustomerId to numeric? I don't see any mentioning in documentation (for both plyr and dplyr) about this type of restriction. Would your code work incorrectly, if the merge column would be of character type (especially interested in plyr)? Am I missing something?

@Ghose Bishwajit 2019-02-24 20:12:30

Could one use semi_join(df1, df2, df3, df4) to keep only observations in df1 that match the rest of the columns?

@abhy3 2019-03-08 13:53:23

@GhoseBishwajit Assuming you mean rest of the dataframes instead of columns, you could use rbind on df2, df3 and df4 if they have same structure e.g. semi_join(df1, rbind(df2, df3, df4))

@Ghose Bishwajit 2019-03-08 21:16:37

Yes I meant dataframe. But they are not the same structure as some are missing on certain rows. For four dataframes, I have data on four different indicators (GDP, GNP GINI, MMR) for different number of countries. I want to join the dataframes in a way that keeps only those countries present for all four indicators.

@bgoldst 2016-06-30 18:11:26

For the case of a left join with a 0..*:0..1 cardinality or a right join with a 0..1:0..* cardinality it is possible to assign in-place the unilateral columns from the joiner (the 0..1 table) directly onto the joinee (the 0..* table), and thereby avoid the creation of an entirely new table of data. This requires matching the key columns from the joinee into the joiner and indexing+ordering the joiner's rows accordingly for the assignment.

If the key is a single column, then we can use a single call to match() to do the matching. This is the case I'll cover in this answer.

Here's an example based on the OP, except I've added an extra row to df2 with an id of 7 to test the case of a non-matching key in the joiner. This is effectively df1 left join df2:

df1 <- data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L)));
df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas'));
df1[names(df2)[-1L]] <- df2[match(df1[,1L],df2[,1L]),-1L];
##   CustomerId Product   State
## 1          1 Toaster    <NA>
## 2          2 Toaster Alabama
## 3          3 Toaster    <NA>
## 4          4   Radio Alabama
## 5          5   Radio    <NA>
## 6          6   Radio    Ohio

In the above I hard-coded an assumption that the key column is the first column of both input tables. I would argue that, in general, this is not an unreasonable assumption, since, if you have a data.frame with a key column, it would be strange if it had not been set up as the first column of the data.frame from the outset. And you can always reorder the columns to make it so. An advantageous consequence of this assumption is that the name of the key column does not have to be hard-coded, although I suppose it's just replacing one assumption with another. Concision is another advantage of integer indexing, as well as speed. In the benchmarks below I'll change the implementation to use string name indexing to match the competing implementations.

I think this is a particularly appropriate solution if you have several tables that you want to left join against a single large table. Repeatedly rebuilding the entire table for each merge would be unnecessary and inefficient.

On the other hand, if you need the joinee to remain unaltered through this operation for whatever reason, then this solution cannot be used, since it modifies the joinee directly. Although in that case you could simply make a copy and perform the in-place assignment(s) on the copy.

As a side note, I briefly looked into possible matching solutions for multicolumn keys. Unfortunately, the only matching solutions I found were:

  • inefficient concatenations. e.g. match(interaction(df1$a,df1$b),interaction(df2$a,df2$b)), or the same idea with paste().
  • inefficient cartesian conjunctions, e.g. outer(df1$a,df2$a,`==`) & outer(df1$b,df2$b,`==`).
  • base R merge() and equivalent package-based merge functions, which always allocate a new table to return the merged result, and thus are not suitable for an in-place assignment-based solution.

For example, see Matching multiple columns on different data frames and getting other column as result, match two columns with two other columns, Matching on multiple columns, and the dupe of this question where I originally came up with the in-place solution, Combine two data frames with different number of rows in R.


I decided to do my own benchmarking to see how the in-place assignment approach compares to the other solutions that have been offered in this question.

Testing code:


solSpecs <- list(
        inner=function(df1,df2,key) merge(df1,df2,key),
        left =function(df1,df2,key) merge(df1,df2,key,all.x=T),
        right=function(df1,df2,key) merge(df1,df2,key,all.y=T),
        full =function(df1,df2,key) merge(df1,df2,key,all=T)
        inner=function(dt1,dt2,key) dt1[dt2,on=key,nomatch=0L,allow.cartesian=T],
        left =function(dt1,dt2,key) dt2[dt1,on=key,allow.cartesian=T],
        right=function(dt1,dt2,key) dt1[dt2,on=key,allow.cartesian=T],
        full =function(dt1,dt2,key) merge(dt1,dt2,key,all=T,allow.cartesian=T) ## calls
        inner=function(dt1,dt2) dt1[dt2,nomatch=0L,allow.cartesian=T],
        left =function(dt1,dt2) dt2[dt1,allow.cartesian=T],
        right=function(dt1,dt2) dt1[dt2,allow.cartesian=T],
        full =function(dt1,dt2) merge(dt1,dt2,all=T,allow.cartesian=T) ## calls
    sqldf.unindexed=list(testFuncs=list( ## note: must pass connection=NULL to avoid running against the live DB connection, which would result in collisions with the residual tables from the last query upload
        inner=function(df1,df2,key) sqldf(paste0('select * from df1 inner join df2 using(',paste(collapse=',',key),')'),connection=NULL),
        left =function(df1,df2,key) sqldf(paste0('select * from df1 left join df2 using(',paste(collapse=',',key),')'),connection=NULL),
        right=function(df1,df2,key) sqldf(paste0('select * from df2 left join df1 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do right join proper, not yet supported; inverted left join is equivalent
        ##full =function(df1,df2,key) sqldf(paste0('select * from df1 full join df2 using(',paste(collapse=',',key),')'),connection=NULL) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing
    sqldf.indexed=list(testFuncs=list( ## important: requires an active DB connection with preindexed main.df1 and main.df2 ready to go; arguments are actually ignored
        inner=function(df1,df2,key) sqldf(paste0('select * from main.df1 inner join main.df2 using(',paste(collapse=',',key),')')),
        left =function(df1,df2,key) sqldf(paste0('select * from main.df1 left join main.df2 using(',paste(collapse=',',key),')')),
        right=function(df1,df2,key) sqldf(paste0('select * from main.df2 left join main.df1 using(',paste(collapse=',',key),')')) ## can't do right join proper, not yet supported; inverted left join is equivalent
        ##full =function(df1,df2,key) sqldf(paste0('select * from main.df1 full join main.df2 using(',paste(collapse=',',key),')')) ## can't do full join proper, not yet supported; possible to hack it with a union of left joins, but too unreasonable to include in testing
        inner=function(df1,df2,key) join(df1,df2,key,'inner'),
        left =function(df1,df2,key) join(df1,df2,key,'left'),
        right=function(df1,df2,key) join(df1,df2,key,'right'),
        full =function(df1,df2,key) join(df1,df2,key,'full')
        inner=function(df1,df2,key) inner_join(df1,df2,key),
        left =function(df1,df2,key) left_join(df1,df2,key),
        right=function(df1,df2,key) right_join(df1,df2,key),
        full =function(df1,df2,key) full_join(df1,df2,key)
        left =function(df1,df2,key) { cns <- setdiff(names(df2),key); df1[cns] <- df2[match(df1[,key],df2[,key]),cns]; df1; },
        right=function(df1,df2,key) { cns <- setdiff(names(df1),key); df2[cns] <- df1[match(df2[,key],df1[,key]),cns]; df2; }

getSolTypes <- function() names(solSpecs);
getJoinTypes <- function() unique(unlist(lapply(solSpecs,function(x) names(x$testFuncs))));
getArgSpec <- function(argSpecs,key=NULL) if (is.null(key)) argSpecs$default else argSpecs[[key]];

initSqldf <- function() {
    sqldf(); ## creates sqlite connection on first run, cleans up and closes existing connection otherwise
    if (exists('sqldfInitFlag',envir=globalenv(),inherits=F) && sqldfInitFlag) { ## false only on first run
        sqldf(); ## creates a new connection
    } else {
        assign('sqldfInitFlag',T,envir=globalenv()); ## set to true for the one and only time
    }; ## end if
}; ## end initSqldf()

setUpBenchmarkCall <- function(argSpecs,joinType,solTypes=getSolTypes(),env=parent.frame()) {
    ## builds and returns a list of expressions suitable for passing to the list argument of microbenchmark(), and assigns variables to resolve symbol references in those expressions
    callExpressions <- list();
    nms <- character();
    for (solType in solTypes) {
        testFunc <- solSpecs[[solType]]$testFuncs[[joinType]];
        if (is.null(testFunc)) next; ## this join type is not defined for this solution type
        testFuncName <- paste0('tf.',solType);
        argSpecKey <- solSpecs[[solType]]$argSpec;
        argSpec <- getArgSpec(argSpecs,argSpecKey);
        argList <- setNames(nm=names(argSpec$args),vector('list',length(argSpec$args)));
        for (i in seq_along(argSpec$args)) {
            argName <- paste0('tfa.',argSpecKey,i);
            argList[[i]] <- if (i%in%argSpec$copySpec) call('copy',as.symbol(argName)) else as.symbol(argName);
        }; ## end for
        callExpressions[[length(callExpressions)+1L]] <-,c(list(testFuncName),argList),quote=T);
        nms[length(nms)+1L] <- solType;
    }; ## end for
    names(callExpressions) <- nms;
}; ## end setUpBenchmarkCall()

harmonize <- function(res) {
    res <-; ## coerce to data.frame
    for (ci in which(sapply(res,is.factor))) res[[ci]] <- as.character(res[[ci]]); ## coerce factor columns to character
    for (ci in which(sapply(res,is.logical))) res[[ci]] <- as.integer(res[[ci]]); ## coerce logical columns to integer (works around sqldf quirk of munging logicals to integers)
    ##for (ci in which(sapply(res,inherits,'POSIXct'))) res[[ci]] <- as.double(res[[ci]]); ## coerce POSIXct columns to double (works around sqldf quirk of losing POSIXct class) ----- POSIXct doesn't work at all in sqldf.indexed
    res <- res[order(names(res))]; ## order columns
    res <- res[,res),]; ## order rows
}; ## end harmonize()

checkIdentical <- function(argSpecs,solTypes=getSolTypes()) {
    for (joinType in getJoinTypes()) {
        callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes);
        if (length(callExpressions)<2L) next;
        ex <- harmonize(eval(callExpressions[[1L]]));
        for (i in seq(2L,len=length(callExpressions)-1L)) {
            y <- harmonize(eval(callExpressions[[i]]));
            if (!isTRUE(all.equal(ex,y,check.attributes=F))) {
                ex <<- ex;
                y <<- y;
                solType <- names(callExpressions)[i];
                stop(paste0('non-identical: ',solType,' ',joinType,'.'));
            }; ## end if
        }; ## end for
    }; ## end for
}; ## end checkIdentical()

testJoinType <- function(argSpecs,joinType,solTypes=getSolTypes(),metric=NULL,times=100L) {
    callExpressions <- setUpBenchmarkCall(argSpecs,joinType,solTypes);
    bm <- microbenchmark(list=callExpressions,times=times);
    if (is.null(metric)) return(bm);
    bm <- summary(bm);
    res <- setNames(nm=names(callExpressions),bm[[metric]]);
    attr(res,'unit') <- attr(bm,'unit');
}; ## end testJoinType()

testAllJoinTypes <- function(argSpecs,solTypes=getSolTypes(),metric=NULL,times=100L) {
    joinTypes <- getJoinTypes();
    resList <- setNames(nm=joinTypes,lapply(joinTypes,function(joinType) testJoinType(argSpecs,joinType,solTypes,metric,times)));
    if (is.null(metric)) return(resList);
    units <- unname(unlist(lapply(resList,attr,'unit')));
    res <-,c(list(join=joinTypes),setNames(nm=solTypes,rep(list(rep(NA_real_,length(joinTypes))),length(solTypes))),list(unit=units,stringsAsFactors=F)));
    for (i in seq_along(resList)) res[i,match(names(resList[[i]]),names(res))] <- resList[[i]];
}; ## end testAllJoinTypes()

testGrid <- function(makeArgSpecsFunc,sizes,overlaps,solTypes=getSolTypes(),joinTypes=getJoinTypes(),metric='median',times=100L) {

    res <- expand.grid(size=sizes,overlap=overlaps,joinType=joinTypes,stringsAsFactors=F);
    res[solTypes] <- NA_real_;
    res$unit <- NA_character_;
    for (ri in seq_len(nrow(res))) {

        size <- res$size[ri];
        overlap <- res$overlap[ri];
        joinType <- res$joinType[ri];

        argSpecs <- makeArgSpecsFunc(size,overlap);


        cur <- testJoinType(argSpecs,joinType,solTypes,metric,times);
        res[ri,match(names(cur),names(res))] <- cur;
        res$unit[ri] <- attr(cur,'unit');

    }; ## end for


}; ## end testGrid()

Here's a benchmark of the example based on the OP that I demonstrated earlier:

## OP's example, supplemented with a non-matching row in df2
argSpecs <- list(
        df1 <- data.frame(CustomerId=1:6,Product=c(rep('Toaster',3L),rep('Radio',3L))),
        df2 <- data.frame(CustomerId=c(2L,4L,6L,7L),State=c(rep('Alabama',2L),'Ohio','Texas')),
## prepare sqldf
sqldf('create index df1_key on df1(CustomerId);'); ## upload and create an sqlite index on df1
sqldf('create index df2_key on df2(CustomerId);'); ## upload and create an sqlite index on df2


##    join    merge data.table.unkeyed data.table.keyed sqldf.unindexed sqldf.indexed      plyr    dplyr         unit
## 1 inner  644.259           861.9345          923.516        9157.752      1580.390  959.2250 270.9190       NA microseconds
## 2  left  713.539           888.0205          910.045        8820.334      1529.714  968.4195 270.9185 224.3045 microseconds
## 3 right 1221.804           909.1900          923.944        8930.668      1533.135 1063.7860 269.8495 218.1035 microseconds
## 4  full 1302.203          3107.5380         3184.729              NA            NA 1593.6475 270.7055       NA microseconds

Here I benchmark on random input data, trying different scales and different patterns of key overlap between the two input tables. This benchmark is still restricted to the case of a single-column integer key. As well, to ensure that the in-place solution would work for both left and right joins of the same tables, all random test data uses 0..1:0..1 cardinality. This is implemented by sampling without replacement the key column of the first data.frame when generating the key column of the second data.frame.

makeArgSpecs.singleIntegerKey.optionalOneToOne <- function(size,overlap) {

    com <- as.integer(size*overlap);

    argSpecs <- list(
            df1 <- data.frame(id=sample(size),y1=rnorm(size),y2=rnorm(size)),
            df2 <- data.frame(id=sample(c(if (com>0L) sample(df1$id,com) else integer(),seq(size+1L,len=size-com))),y3=rnorm(size),y4=rnorm(size)),
    ## prepare sqldf
    sqldf('create index df1_key on df1(id);'); ## upload and create an sqlite index on df1
    sqldf('create index df2_key on df2(id);'); ## upload and create an sqlite index on df2


}; ## end makeArgSpecs.singleIntegerKey.optionalOneToOne()

## cross of various input sizes and key overlaps
sizes <- c(1e1L,1e3L,1e6L);
overlaps <- c(0.99,0.5,0.01);
system.time({ res <- testGrid(makeArgSpecs.singleIntegerKey.optionalOneToOne,sizes,overlaps); });
##     user   system  elapsed
## 22024.65 12308.63 34493.19

I wrote some code to create log-log plots of the above results. I generated a separate plot for each overlap percentage. It's a little bit cluttered, but I like having all the solution types and join types represented in the same plot.

I used spline interpolation to show a smooth curve for each solution/join type combination, drawn with individual pch symbols. The join type is captured by the pch symbol, using a dot for inner, left and right angle brackets for left and right, and a diamond for full. The solution type is captured by the color as shown in the legend.

plotRes <- function(res,titleFunc,useFloor=F) {
    solTypes <- setdiff(names(res),c('size','overlap','joinType','unit')); ## derive from res
    normMult <- c(microseconds=1e-3,milliseconds=1); ## normalize to milliseconds
    joinTypes <- getJoinTypes();
    cols <- c(merge='purple',data.table.unkeyed='blue',data.table.keyed='#00DDDD',sqldf.unindexed='brown',sqldf.indexed='orange',plyr='red',dplyr='#00BB00','magenta');
    pchs <- list(inner=20L,left='<',right='>',full=23L);
    cexs <- c(inner=0.7,left=1,right=1,full=0.7);
    NP <- 60L;
    ord <- order(decreasing=T,colMeans(res[res$size==max(res$size),solTypes],na.rm=T));
    ymajors <- data.frame(y=c(1,1e3),label=c('1ms','1s'),stringsAsFactors=F);
    for (overlap in unique(res$overlap)) {
        x1 <- res[res$overlap==overlap,];
        x1[solTypes] <- x1[solTypes]*normMult[x1$unit]; x1$unit <- NULL;
        xlim <- c(1e1,max(x1$size));
        xticks <- 10^seq(log10(xlim[1L]),log10(xlim[2L]));
        ylim <- c(1e-1,10^((if (useFloor) floor else ceiling)(log10(max(x1[solTypes],na.rm=T))))); ## use floor() to zoom in a little more, only sqldf.unindexed will break above, but xpd=NA will keep it visible
        yticks <- 10^seq(log10(ylim[1L]),log10(ylim[2L]));
        yticks.minor <- rep(yticks[-length(yticks)],each=9L)*1:9;
        plot(NA,xlim=xlim,ylim=ylim,xaxs='i',yaxs='i',axes=F,xlab='size (rows)',ylab='time (ms)',log='xy');
        for (joinType in rev(joinTypes)) { ## reverse to draw full first, since it's larger and would be more obtrusive if drawn last
            x2 <- x1[x1$joinType==joinType,];
            for (solType in solTypes) {
                if (any(![[solType]]))) {
                    xy <- spline(x2$size,x2[[solType]],xout=10^(seq(log10(x2$size[1L]),log10(x2$size[nrow(x2)]),len=NP)));
                }; ## end if
            }; ## end for
        }; ## end for
        ## custom legend
        ## due to logarithmic skew, must do all distance calcs in inches, and convert to user coords afterward
        ## the bottom-left corner of the legend will be defined in normalized figure coords, although we can convert to inches immediately
        leg.cex <- 0.7; <- grconvertX(0.275,'nfc','in'); <- grconvertY(0.6,'nfc','in');
        leg.x.user <- grconvertX(,'in');
        leg.y.user <- grconvertY(,'in'); <- 0.1; <- 0.1; <- 0.1; <- 0.1; <- max(strwidth(solTypes,'in',leg.cex)); <- max(strheight(solTypes,'in',leg.cex))*1.5; ## multiplication factor for greater line height <- max(strheight(joinTypes,'in',leg.cex))*1.5; ## ditto <- max(strwidth(joinTypes,'in',leg.cex)); <-*length(joinTypes); <-*length(solTypes);
        leg.x2.user <- grconvertX(*,'in');
        leg.y2.user <- grconvertY(*,'in');
        leg.cols.x.user <- grconvertX(*(0.5+seq(0L,length(joinTypes)-1L)),'in');
        leg.lines.y.user <- grconvertY(*(0.5+seq(0L,length(solTypes)-1L)),'in');
        leg.sol.x.user <- grconvertX(,'in');
        leg.join.y.user <- grconvertY(,'in');
        text(leg.cols.x.user,leg.join.y.user,joinTypes,cex=leg.cex,pos=4L,offset=0,srt=90); ## srt rotation applies *after* pos/offset positioning
        for (i in seq_along(joinTypes)) {
            joinType <- joinTypes[i];
        }; ## end for
        readline(sprintf('overlap %.02f',overlap));
    }; ## end for
}; ## end plotRes()

titleFunc <- function(overlap) sprintf('R merge solutions: single-column integer key, 0..1:0..1 cardinality, %d%% overlap',as.integer(overlap*100));




Here's a second large-scale benchmark that's more heavy-duty, with respect to the number and types of key columns, as well as cardinality. For this benchmark I use three key columns: one character, one integer, and one logical, with no restrictions on cardinality (that is, 0..*:0..*). (In general it's not advisable to define key columns with double or complex values due to floating-point comparison complications, and basically no one ever uses the raw type, much less for key columns, so I haven't included those types in the key columns. Also, for information's sake, I initially tried to use four key columns by including a POSIXct key column, but the POSIXct type didn't play well with the sqldf.indexed solution for some reason, possibly due to floating-point comparison anomalies, so I removed it.)

makeArgSpecs.assortedKey.optionalManyToMany <- function(size,overlap,uniquePct=75) {

    ## number of unique keys in df1
    u1Size <- as.integer(size*uniquePct/100);

    ## (roughly) divide u1Size into bases, so we can use expand.grid() to produce the required number of unique key values with repetitions within individual key columns
    ## use ceiling() to ensure we cover u1Size; will truncate afterward
    u1SizePerKeyColumn <- as.integer(ceiling(u1Size^(1/3)));

    ## generate the unique key values for df1
    keys1 <- expand.grid(stringsAsFactors=F,
        ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+sample(u1SizePerKeyColumn)

    ## rbind some repetitions of the unique keys; this will prepare one side of the many-to-many relationship
    ## also scramble the order afterward
    keys1 <- rbind(keys1,keys1[sample(nrow(keys1),size-u1Size,T),])[sample(size),];

    ## common and unilateral key counts
    com <- as.integer(size*overlap);
    uni <- size-com;

    ## generate some unilateral keys for df2 by synthesizing outside of the idInteger range of df1
    keys2 <- data.frame(stringsAsFactors=F,
        ##idPOSIXct=as.POSIXct('2016-01-01 00:00:00','UTC')+u1SizePerKeyColumn+sample(uni)

    ## rbind random keys from df1; this will complete the many-to-many relationship
    ## also scramble the order afterward
    keys2 <- rbind(keys2,keys1[sample(nrow(keys1),com,T),])[sample(size),];

    ##keyNames <- c('idCharacter','idInteger','idLogical','idPOSIXct');
    keyNames <- c('idCharacter','idInteger','idLogical');
    ## note: was going to use raw and complex type for two of the non-key columns, but data.table doesn't seem to fully support them
    argSpecs <- list(
            df1 <- cbind(stringsAsFactors=F,keys1,y1=sample(c(F,T),size,T),y2=sample(size),y3=rnorm(size),y4=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))),
            df2 <- cbind(stringsAsFactors=F,keys2,y5=sample(c(F,T),size,T),y6=sample(size),y7=rnorm(size),y8=replicate(size,paste(collapse='',sample(letters,sample(4:12,1L),T)))),
    ## prepare sqldf
    sqldf(paste0('create index df1_key on df1(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df1
    sqldf(paste0('create index df2_key on df2(',paste(collapse=',',keyNames),');')); ## upload and create an sqlite index on df2


}; ## end makeArgSpecs.assortedKey.optionalManyToMany()

sizes <- c(1e1L,1e3L,1e5L); ## 1e5L instead of 1e6L to respect more heavy-duty inputs
overlaps <- c(0.99,0.5,0.01);
solTypes <- setdiff(getSolTypes(),'');
system.time({ res <- testGrid(makeArgSpecs.assortedKey.optionalManyToMany,sizes,overlaps,solTypes); });
##     user   system  elapsed
## 38895.50   784.19 39745.53

The resulting plots, using the same plotting code given above:

titleFunc <- function(overlap) sprintf('R merge solutions: character/integer/logical key, 0..*:0..* cardinality, %d%% overlap',as.integer(overlap*100));




@jangorecki 2018-12-31 15:48:53

very nice analysis, but it is a pity you set scale from 10^1 to 10^6, those are so tiny sets that speed difference is almost irrelevant. 10^6 to 10^8 would be interesting to see!

@jangorecki 2019-02-07 07:24:52

I also spotted you include timing of class coercion in benchmark which makes it invalid for join operation.

@maj 2014-01-29 17:43:26

New in 2014:

Especially if you're also interested in data manipulation in general (including sorting, filtering, subsetting, summarizing etc.), you should definitely take a look at dplyr, which comes with a variety of functions all designed to facilitate your work specifically with data frames and certain other database types. It even offers quite an elaborate SQL interface, and even a function to convert (most) SQL code directly into R.

The four joining-related functions in the dplyr package are (to quote):

  • inner_join(x, y, by = NULL, copy = FALSE, ...): return all rows from x where there are matching values in y, and all columns from x and y
  • left_join(x, y, by = NULL, copy = FALSE, ...): return all rows from x, and all columns from x and y
  • semi_join(x, y, by = NULL, copy = FALSE, ...): return all rows from x where there are matching values in y, keeping just columns from x.
  • anti_join(x, y, by = NULL, copy = FALSE, ...): return all rows from x where there are not matching values in y, keeping just columns from x

It's all here in great detail.

Selecting columns can be done by select(df,"column"). If that's not SQL-ish enough for you, then there's the sql() function, into which you can enter SQL code as-is, and it will do the operation you specified just like you were writing in R all along (for more information, please refer to the dplyr/databases vignette). For example, if applied correctly, sql("SELECT * FROM hflights") will select all the columns from the "hflights" dplyr table (a "tbl").

@Marco Fumagalli 2018-06-25 08:44:34

Definitely best solution given the importance that dplyr package has gained over the last two years.

@medriscoll 2009-08-20 17:54:49

I would recommend checking out Gabor Grothendieck's sqldf package, which allows you to express these operations in SQL.


## inner join
df3 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              JOIN df2 USING(CustomerID)")

## left join (substitute 'right' for right join)
df4 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              LEFT JOIN df2 USING(CustomerID)")

I find the SQL syntax to be simpler and more natural than its R equivalent (but this may just reflect my RDBMS bias).

See Gabor's sqldf GitHub for more information on joins.

@BradP 2015-02-26 18:11:13

In joining two data frames with ~1 million rows each, one with 2 columns and the other with ~20, I've surprisingly found merge(..., all.x = TRUE, all.y = TRUE) to be faster then dplyr::full_join(). This is with dplyr v0.4

Merge takes ~17 seconds, full_join takes ~65 seconds.

Some food for though, since I generally default to dplyr for manipulation tasks.

@JD Long 2009-08-19 15:15:10

There are some good examples of doing this over at the R Wiki. I'll steal a couple here:

Merge Method

Since your keys are named the same the short way to do an inner join is merge():


a full inner join (all records from both tables) can be created with the "all" keyword:

merge(df1,df2, all=TRUE)

a left outer join of df1 and df2:

merge(df1,df2, all.x=TRUE)

a right outer join of df1 and df2:

merge(df1,df2, all.y=TRUE)

you can flip 'em, slap 'em and rub 'em down to get the other two outer joins you asked about :)

Subscript Method

A left outer join with df1 on the left using a subscript method would be:

df1[,"State"]<-df2[df1[ ,"Product"], "State"]

The other combination of outer joins can be created by mungling the left outer join subscript example. (yeah, I know that's the equivalent of saying "I'll leave it as an exercise for the reader...")

@zx8754 2019-02-06 13:03:44

"R Wiki" link is broken.

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

43 Answered Questions

[SOLVED] How to merge two dictionaries in a single expression?

3 Answered Questions

22 Answered Questions

[SOLVED] How do you merge two Git repositories?

8 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

19 Answered Questions


  • 2010-08-04 11:18:21
  • Toy
  • 556041 View
  • 439 Score
  • 19 Answer
  • Tags:   c# linq join

3 Answered Questions

[SOLVED] Why does X[Y] join of data.tables not allow a full outer join, or a left join?

  • 2012-10-07 23:46:03
  • Douglas Clark
  • 26727 View
  • 109 Score
  • 3 Answer
  • Tags:   r join data.table

19 Answered Questions

[SOLVED] Drop data frame columns by name

  • 2011-01-05 14:34:29
  • Btibert3
  • 1168936 View
  • 744 Score
  • 19 Answer
  • Tags:   r dataframe r-faq

10 Answered Questions


10 Answered Questions

[SOLVED] What is the difference between Left, Right, Outer and Inner Joins?

  • 2009-01-15 19:07:31
  • MrM
  • 351146 View
  • 524 Score
  • 10 Answer
  • Tags:   sql database join

Sponsored Content