How to Do an Outer Join in R (With Examples)

There are two common ways to perform an outer join in R:

Method 1: Use Base R

merge(df1, df2, by='column_to_join_on', all=TRUE)

Method 2: Use dplyr

library(dplyr)

full_join(df1, df2, by='column_to_join_on')

Each method will return all rows from both tables.

Both methods will produce the same result, but the dplyr method will tend to work faster on extremely large datasets.

The following examples show how to use each of these functions in practice with the following data frames:

#define first data frame
df1 = data.frame(team=c('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H'),
                 points=c(18, 22, 19, 14, 14, 11, 20, 28))

df1

  team points
1    A     18
2    B     22
3    C     19
4    D     14
5    E     14
6    F     11
7    G     20
8    H     28

#define second data frame
df2 = data.frame(team=c('A', 'B', 'C', 'D', 'L', 'M'),
                 assists=c(4, 9, 14, 13, 10, 8))

df2

  team assists
1    A       4
2    B       9
3    C      14
4    D      13
5    L      10
6    M       8

Example 1: Outer Join Using Base R

We can use the merge() function in base R to perform an outer join, using the ‘team’ column as the column to join on:

#perform outer join using base R
df3 <- merge(df1, df2, by='team', all=TRUE)

#view result
df3

   team points assists
1     A     18       4
2     B     22       9
3     C     19      14
4     D     14      13
5     E     14      NA
6     F     11      NA
7     G     20      NA
8     H     28      NA
9     L     NA      10
10    M     NA       8

Notice that all of the rows from both data frames are returned.

Example 2: Outer Join Using dplyr

We can use the full_join() function from the dplyr package to perform an outer join, using the ‘team’ column as the column to join on:

library(dplyr)

#perform outer join using dplyr 
df3 <- full_join(df1, df2, by='team')

#view result
df3

   team points assists
1     A     18       4
2     B     22       9
3     C     19      14
4     D     14      13
5     E     14      NA
6     F     11      NA
7     G     20      NA
8     H     28      NA
9     L     NA      10
10    M     NA       8

Notice that this matches the result we obtained from using the merge() function in base R.