R Academy · Lesson

Multi-table Joins in dplyr

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

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')

All lessons in this course

← Back to R Academy