How do I join two tables?

Line two tables up on a shared column with merge and with the dplyr join verbs, and control what happens to the rows that have no match.

A join lines two tables up on a column they share. One table holds prices by ticker, the other holds a sector for each ticker, and the join puts the sector next to the price.

In this lesson I join a three-row price table onto a three-row sector table, first with base R’s merge() and then with the four dplyr join verbs, and I show what each one does to the rows that find no match.

Step 1. Two small tables

prices.csv is simulated data: 40 business days of closes for AAA, CCC and DDD. read.csv() hands Date back as text, so I convert it with as.Date() and then keep the first three rows, which is one day of prices for the three tickers.

prices <- read.csv("prices.csv")                        # 120 rows, simulated
prices$Date <- as.Date(prices$Date)                     # text to real dates

px <- head(prices[, c("Date", "Ticker", "Close")], 3)   # one day, three tickers
print(px)
        Date Ticker  Close
1 2026-01-02    AAA 186.66
2 2026-01-02    CCC 416.98
3 2026-01-02    DDD 125.18
# ->         Date Ticker  Close
# -> 1 2026-01-02    AAA 186.66
# -> 2 2026-01-02    CCC 416.98
# -> 3 2026-01-02    DDD 125.18

The second table I type by hand. It gives a sector for AAA and CCC, says nothing about DDD, and names BBB, which the price file does not contain.

sectors <- data.frame(
  Ticker = c("AAA", "CCC", "BBB"),                    # the shared column
  Sector = c("Hardware", "Software", "Healthcare")
)
print(sectors)
  Ticker     Sector
1    AAA   Hardware
2    CCC   Software
3    BBB Healthcare
# ->   Ticker     Sector
# -> 1    AAA   Hardware
# -> 2    CCC   Software
# -> 3    BBB Healthcare

Ticker appears in both tables. That is the key the join works on. The two gaps, DDD with no sector and BBB with no price, are what the rest of the lesson is about.

Step 2. merge() in base R

merge(x, y, by = "Ticker") puts the two tables side by side wherever the tickers agree. all = FALSE is the default and keeps only the rows that matched.

print(merge(px, sectors, by = "Ticker", all = FALSE))   # matched rows only
  Ticker       Date  Close   Sector
1    AAA 2026-01-02 186.66 Hardware
2    CCC 2026-01-02 416.98 Software
# ->   Ticker       Date  Close   Sector
# -> 1    AAA 2026-01-02 186.66 Hardware
# -> 2    CCC 2026-01-02 416.98 Software

Three price rows went in and two came out. DDD was dropped because sectors has no line for it. merge() also moves the key to the first column and sorts the result by it.

all.x = TRUE says keep every row of the left table whether it matched or not.

m <- merge(px, sectors, by = "Ticker", all.x = TRUE)    # keep all of px
print(m)
  Ticker       Date  Close   Sector
1    AAA 2026-01-02 186.66 Hardware
2    CCC 2026-01-02 416.98 Software
3    DDD 2026-01-02 125.18     <NA>
# ->   Ticker       Date  Close   Sector
# -> 1    AAA 2026-01-02 186.66 Hardware
# -> 2    CCC 2026-01-02 416.98 Software
# -> 3    DDD 2026-01-02 125.18     <NA>

DDD survived, and R filled the column it could not fill with NA. The <NA> tells you the ticker had no line in the sector table, rather than silently losing the row.

print(m$Ticker[is.na(m$Sector)])                        # which tickers got no sector
[1] "DDD"
# -> [1] "DDD"

Step 3. The four dplyr joins

dplyr gives the same four behaviours one verb each. by = "Ticker" names the key. Leave it out and dplyr guesses from the shared column names and prints a message about it, so I always write it.

inner_join() keeps only rows whose key is in both tables.

library(dplyr)

print(inner_join(px, sectors, by = "Ticker"))           # in both tables
        Date Ticker  Close   Sector
1 2026-01-02    AAA 186.66 Hardware
2 2026-01-02    CCC 416.98 Software
# ->         Date Ticker  Close   Sector
# -> 1 2026-01-02    AAA 186.66 Hardware
# -> 2 2026-01-02    CCC 416.98 Software

left_join() keeps every row of the left table and fills the misses with NA.

print(left_join(px, sectors, by = "Ticker"))            # all of px
        Date Ticker  Close   Sector
1 2026-01-02    AAA 186.66 Hardware
2 2026-01-02    CCC 416.98 Software
3 2026-01-02    DDD 125.18     <NA>
# ->         Date Ticker  Close   Sector
# -> 1 2026-01-02    AAA 186.66 Hardware
# -> 2 2026-01-02    CCC 416.98 Software
# -> 3 2026-01-02    DDD 125.18     <NA>

right_join() keeps every row of the right table instead. BBB comes in with no date and no close.

print(right_join(px, sectors, by = "Ticker"))           # all of sectors
        Date Ticker  Close     Sector
1 2026-01-02    AAA 186.66   Hardware
2 2026-01-02    CCC 416.98   Software
3       <NA>    BBB     NA Healthcare
# ->         Date Ticker  Close     Sector
# -> 1 2026-01-02    AAA 186.66   Hardware
# -> 2 2026-01-02    CCC 416.98   Software
# -> 3       <NA>    BBB     NA Healthcare

full_join() keeps every row of both, so DDD and BBB both appear with a hole.

print(full_join(px, sectors, by = "Ticker"))            # all of both
        Date Ticker  Close     Sector
1 2026-01-02    AAA 186.66   Hardware
2 2026-01-02    CCC 416.98   Software
3 2026-01-02    DDD 125.18       <NA>
4       <NA>    BBB     NA Healthcare
# ->         Date Ticker  Close     Sector
# -> 1 2026-01-02    AAA 186.66   Hardware
# -> 2 2026-01-02    CCC 416.98   Software
# -> 3 2026-01-02    DDD 125.18       <NA>
# -> 4       <NA>    BBB     NA Healthcare

Four verbs, four row counts, same two input tables.

print(c(inner = nrow(inner_join(px, sectors, by = "Ticker")),   # 2 matched
        left  = nrow(left_join(px, sectors, by = "Ticker")),    # 3 in px
        right = nrow(right_join(px, sectors, by = "Ticker")),   # 3 in sectors
        full  = nrow(full_join(px, sectors, by = "Ticker"))))   # 4 in either
inner  left right  full 
    2     3     3     4 
# -> inner  left right  full
# ->     2     3     3     4

left_join() is the one I reach for on price data. It keeps the price table intact and adds columns to it.

Step 4. anti_join finds what did not match

anti_join(x, y) returns the rows of x whose key is missing from y, and adds no columns. It answers “which tickers have no sector” directly.

print(anti_join(px, sectors, by = "Ticker"))            # in px, not in sectors
        Date Ticker  Close
1 2026-01-02    DDD 125.18
# ->         Date Ticker  Close
# -> 1 2026-01-02    DDD 125.18

print(anti_join(sectors, px, by = "Ticker"))            # in sectors, not in px
  Ticker     Sector
1    BBB Healthcare
# ->   Ticker     Sector
# -> 1    BBB Healthcare

Swap the two arguments and you get the other side of the mismatch.

Step 5. A join can change the number of rows

If a key appears twice in the right table, every match on the left is copied once per hit. Here AAA is listed under two sectors.

sectors2 <- data.frame(
  Ticker = c("AAA", "AAA", "CCC"),                   # AAA twice, key not unique
  Sector = c("Hardware", "Consumer", "Software")
)

dup <- left_join(px, sectors2, by = "Ticker")           # left join, 3 rows in
print(dup)
        Date Ticker  Close   Sector
1 2026-01-02    AAA 186.66 Hardware
2 2026-01-02    AAA 186.66 Consumer
3 2026-01-02    CCC 416.98 Software
4 2026-01-02    DDD 125.18     <NA>
# ->         Date Ticker  Close   Sector
# -> 1 2026-01-02    AAA 186.66 Hardware
# -> 2 2026-01-02    AAA 186.66 Consumer
# -> 3 2026-01-02    CCC 416.98 Software
# -> 4 2026-01-02    DDD 125.18     <NA>

print(c(before = nrow(px), after = nrow(dup)))          # 3 rows became 4
before  after 
     3      4 
# -> before  after
# ->      3      4

Compare nrow() before and after every join. A left join that grows the table means the key is not unique on the right, and the extra rows will double count anything you sum later.

Step 6. Tag the whole price file

Now the same left join on all 120 rows. The key is unique in sectors, so the row count must not move.

tagged <- prices |>
  left_join(sectors, by = "Ticker")                     # add Sector to every price row

print(c(before = nrow(prices), after = nrow(tagged)))   # unchanged, as it should be
before  after 
   120    120 
# -> before  after
# ->    120    120

print(head(tagged, 4))
        Date Ticker  Close  Volume   Sector
1 2026-01-02    AAA 186.66 2183494 Hardware
2 2026-01-02    CCC 416.98 3117814 Software
3 2026-01-02    DDD 125.18 7447373     <NA>
4 2026-01-05    AAA 186.38 8463199 Hardware
# ->         Date Ticker  Close  Volume   Sector
# -> 1 2026-01-02    AAA 186.66 2183494 Hardware
# -> 2 2026-01-02    CCC 416.98 3117814 Software
# -> 3 2026-01-02    DDD 125.18 7447373     <NA>
# -> 4 2026-01-05    AAA 186.38 8463199 Hardware

DDD carries NA on all 40 of its days, because one missing line in a three-row lookup table costs you 40 rows of the result.

print(sum(is.na(tagged$Sector)))                        # rows with no sector
[1] 40
# -> [1] 40
print(unique(tagged$Ticker[is.na(tagged$Sector)]))      # and the ticker behind them
[1] "DDD"
# -> [1] "DDD"

With Sector on the table, the grouping from Lesson 19 works on it. I drop the unmatched rows first so the NA does not become a group of its own.

by_sector <- tagged |>
  filter(!is.na(Sector)) |>                             # drop DDD, it has no sector
  group_by(Sector) |>
  summarise(rows = n(), mean_close = round(mean(Close), 2)) |>
  as.data.frame()

print(by_sector)
    Sector rows mean_close
1 Hardware   40     183.69
2 Software   40     437.15
# ->     Sector rows mean_close
# -> 1 Hardware   40     183.69
# -> 2 Software   40     437.15

40 rows per sector, one per business day, which is the check that the join did not duplicate anything.

Your turn

Add DDD to sectors as “Semiconductors”, left join the result onto prices, and confirm that nrow() is still 120 and that no Sector is NA.

full_sectors <- rbind(sectors,                                   # stack a new row on
                      data.frame(Ticker = "DDD",
                                 Sector = "Semiconductors"))
print(full_sectors)
# ->   Ticker         Sector
# -> 1   AAA       Hardware
# -> 2   CCC       Software
# -> 3    BBB     Healthcare
# -> 4   DDD Semiconductors

tagged <- prices |>
  left_join(full_sectors, by = "Ticker")                         # all four keys now covered

print(c(before = nrow(prices), after = nrow(tagged)))            # row count must hold
# -> before  after
# ->    120    120

print(sum(is.na(tagged$Sector)))                                 # no holes left
# -> [1] 0

print(head(tagged, 3))
# ->         Date Ticker  Close  Volume         Sector
# -> 1 2026-01-02   AAA 186.66 2183494       Hardware
# -> 2 2026-01-02   CCC 416.98 3117814       Software
# -> 3 2026-01-02   DDD 125.18 7447373 Semiconductors

BBB sits in full_sectors with no prices, and the left join ignores it. That is the point of a left join: the left table sets the rows.