0Pricing
R Academy · Lesson

Multi-table Joins in dplyr

Master inner_join, left_join, right_join, full_join, and anti_join.

Multi-table Joins in dplyr is a free R Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Join Tables?

Real data rarely lives in one table. Relational databases split information across multiple tables to avoid repetition. Joins let you combine tables based on shared keys. dplyr provides SQL-style join functions with a clean, readable syntax.

library(dplyr)

# Two related tables
orders <- data.frame(
  order_id = 1:4,
  customer_id = c(1, 2, 1, 3),
  amount = c(250, 180, 320, 90)
)

customers <- data.frame(
  customer_id = 1:3,
  name = c('Alice','Bob','Carol'),
  city = c('NYC','LA','Chicago')
)

print(orders)
print(customers)

inner_join() — Matching Rows Only

inner_join(x, y, by='key') returns only rows where the key exists in both tables. Rows with no match in the other table are dropped. This is the most common join type.

library(dplyr)

orders <- data.frame(
  order_id = 1:4,
  customer_id = c(1, 2, 1, 99),
  amount = c(250, 180, 320, 90)
)

customers <- data.frame(
  customer_id = c(1, 2, 3),
  name = c('Alice','Bob','Carol')
)

# customer_id = 99 in orders has no match => dropped
# customer_id = 3 in customers has no match => dropped
inner_join(orders, customers, by = 'customer_id')

left_join() — Keep All Left Rows

left_join(x, y, by='key') keeps all rows from x and fills in matching data from y. Rows in x with no match in y get NA for y's columns. The left table drives the result.

library(dplyr)

orders <- data.frame(
  order_id = 1:4,
  customer_id = c(1, 2, 1, 99),
  amount = c(250, 180, 320, 90)
)

customers <- data.frame(
  customer_id = c(1, 2, 3),
  name = c('Alice','Bob','Carol')
)

# Order with customer_id=99 is kept, name = NA
left_join(orders, customers, by = 'customer_id')

right_join() — Keep All Right Rows

right_join(x, y, by='key') keeps all rows from y. Rows in y with no match in x get NA for x's columns. It's the mirror of left_join() — less common since you can swap table order.

library(dplyr)

orders <- data.frame(
  order_id = c(1, 2),
  customer_id = c(1, 2),
  amount = c(250, 180)
)

customers <- data.frame(
  customer_id = c(1, 2, 3),
  name = c('Alice','Bob','Carol')
)

# Carol (customer_id=3) has no orders => NA for order columns
right_join(orders, customers, by = 'customer_id')

full_join() — Keep All Rows

full_join(x, y, by='key') keeps every row from both tables. Non-matching rows from either side get NA for the other table's columns. Use it when you cannot afford to lose any data.

library(dplyr)

q1 <- data.frame(product = c('A','B','C'), q1_sales = c(100, 200, 150))
q2 <- data.frame(product = c('A','C','D'), q2_sales = c(120, 160, 90))

# D appears only in q2, B only in q1 => both kept
full_join(q1, q2, by = 'product')

anti_join() — Find Non-Matches

anti_join(x, y, by='key') returns rows in x that have no match in y. No columns from y are included. It's perfect for finding orphan records, unmatched orders, or items missing from a reference table.

library(dplyr)

all_employees <- data.frame(
  id = 1:5,
  name = c('Alice','Bob','Carol','Dave','Eve')
)

trained <- data.frame(id = c(1, 3, 5))

# Who has NOT completed training?
anti_join(all_employees, trained, by = 'id')

semi_join() — Filter by Match

semi_join(x, y, by='key') returns rows in x that have a match in y, but does NOT add y's columns. It's like inner_join() but only returns x's columns — useful as a filter step.

library(dplyr)

products <- data.frame(
  id = 1:5,
  name = c('Widget','Gadget','Donut','Gizmo','Sprocket'),
  price = c(9.99, 24.99, 1.99, 14.99, 4.99)
)

ordered_products <- data.frame(id = c(1, 3, 5))

# Products that appear in at least one order
semi_join(products, ordered_products, by = 'id')

Joining on Different Column Names

When the key columns have different names in x and y, use the by = c('x_col' = 'y_col') syntax. This maps the column name in the left table to the corresponding column in the right table.

library(dplyr)

orders <- data.frame(
  order_id = 1:3,
  user_id = c(10, 20, 10),
  amount = c(100, 200, 150)
)

users <- data.frame(
  id = c(10, 20, 30),
  username = c('alice','bob','carol')
)

# orders.user_id matches users.id
left_join(orders, users, by = c('user_id' = 'id'))

Joining on Multiple Keys

You can join on multiple columns by passing a vector to by. All specified columns must match for a row to be joined. This handles composite keys where no single column is unique.

library(dplyr)

actual <- data.frame(
  year = c(2023, 2023, 2024, 2024),
  quarter = c('Q1','Q2','Q1','Q2'),
  sales = c(100, 120, 130, 150)
)

target <- data.frame(
  year = c(2023, 2023, 2024, 2024),
  quarter = c('Q1','Q2','Q1','Q2'),
  target = c(110, 115, 125, 145)
)

inner_join(actual, target, by = c('year', 'quarter'))

Chaining Multiple Joins

You can chain multiple joins in a single pipeline using the pipe operator. Build up a denormalized view by adding information from several lookup tables one step at a time.

library(dplyr)

orders <- data.frame(order_id=1:3, cust_id=c(1,2,1), prod_id=c(10,10,20))
customers <- data.frame(cust_id=1:2, name=c('Alice','Bob'))
products <- data.frame(prod_id=c(10,20), product=c('Widget','Gadget'))

orders %>%
  left_join(customers, by = 'cust_id') %>%
  left_join(products, by = 'prod_id')

Handling Duplicate Columns

When both tables have columns with the same name (besides the join key), dplyr adds .x and .y suffixes. You can customize these with the suffix argument, then select or rename as needed.

library(dplyr)

current <- data.frame(id=1:3, value=c(10,20,30), date=Sys.Date())
historic <- data.frame(id=1:3, value=c(8,22,28), date=Sys.Date()-365)

# Both have 'value' and 'date' columns
result <- inner_join(current, historic, by='id',
                     suffix=c('_current','_historic'))
print(names(result))

Quick Check

Which dplyr join returns only rows from the left table that have no matching row in the right table?

Recap: dplyr Joins

Key takeaways for multi-table joins in dplyr:

  • inner_join() — only matching rows from both tables
  • left_join() — all rows from left; NA where no match in right
  • right_join() — all rows from right; NA where no match in left
  • full_join() — all rows from both; NA where no match
  • anti_join() — rows in x with NO match in y (filter tool)
  • semi_join() — rows in x WITH a match in y (filter tool)
  • Use by = c('x_col' = 'y_col') for different column names
  • Use vector for composite keys: by = c('year', 'quarter')
library(dplyr)

# Chain of joins: the typical real-world pattern
orders <- data.frame(oid=1:3, cid=c(1,2,1), pid=c(10,20,10))
customers <- data.frame(cid=1:2, name=c('Alice','Bob'))
products <- data.frame(pid=c(10,20), name=c('Widget','Gadget'), price=c(9.99,19.99))

orders %>%
  inner_join(customers, by='cid') %>%
  inner_join(products, by='pid', suffix=c('','_product')) %>%
  select(oid, name, name_product, price)

Frequently asked questions

Is the “Multi-table Joins in dplyr” lesson free?

Yes — the full text of “Multi-table Joins in dplyr” is free to read here on the web, and the R Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the R Academy course, upgrade to CoddyKit PRO.

What will I learn in “Multi-table Joins in dplyr”?

Master inner_join, left_join, right_join, full_join, and anti_join. You practise R Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start R Academy?

No prior experience is required. R Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Multi-table Joins in dplyr” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this R Academy lesson?

Yes. Every R Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Grouped Summaries and group_by()
  2. Window Functions: lag, lead, cumsum
  3. Multi-table Joins in dplyr
  4. Tidy Evaluation: {{ }} and .data
← Back to R Academy