R Joins in dplyr

Joins combine two data frames by matching rows based on a shared column (called a key). Real data is almost always spread across multiple tables — a customers table, an orders table, a products table. Joins let you bring related data together for analysis.

The Two Tables

library(dplyr)

customers <- data.frame(
  cust_id = c(1, 2, 3, 4),
  name    = c("Asha","Balu","Cena","Dev"),
  city    = c("Delhi","Mumbai","Pune","Chennai")
)

orders <- data.frame(
  order_id = c(101, 102, 103, 104, 105),
  cust_id  = c(1, 2, 2, 3, 5),      # customer 4 has no order; customer 5 doesn't exist
  amount   = c(500, 300, 700, 200, 400)
)

Join Types — Visual

customers:  1 Asha   2 Balu   3 Cena   4 Dev
                                              ← no orders
orders:     1→       2→  2→   3→       5→    ← unknown customer

inner_join: 1 Asha   2 Balu   2 Balu   3 Cena     (matched only)
left_join:  1 Asha   2 Balu   2 Balu   3 Cena   4 Dev  (all customers)
right_join: 1 Asha   2 Balu   2 Balu   3 Cena        5 NA  (all orders)
full_join:  All rows from both, NAs where no match

inner_join — Only Matched Rows

inner_join(customers, orders, by="cust_id")
#   cust_id   name    city order_id amount
# 1       1   Asha   Delhi      101    500
# 2       2   Balu  Mumbai      102    300
# 3       2   Balu  Mumbai      103    700
# 4       3   Cena    Pune      104    200
# Dev (4) and unknown (5) are excluded

left_join — All Left Table Rows

left_join(customers, orders, by="cust_id")
# All customers kept; Dev gets NA for order columns
#   cust_id  name    city  order_id amount
# 1       1  Asha   Delhi       101    500
# 2       2  Balu  Mumbai       102    300
# 3       2  Balu  Mumbai       103    700
# 4       3  Cena    Pune       104    200
# 5       4   Dev Chennai        NA     NA

right_join — All Right Table Rows

right_join(customers, orders, by="cust_id")
# All orders kept; customer 5 gets NA for customer columns
#   cust_id   name    city order_id amount
# 1       1   Asha   Delhi      101    500
# 2       2   Balu  Mumbai      102    300
# 3       2   Balu  Mumbai      103    700
# 4       3   Cena    Pune      104    200
# 5       5     NA      NA      105    400

full_join — All Rows From Both

full_join(customers, orders, by="cust_id")
# Dev (cust 4) and unknown (cust 5) both included with NAs

Filtering Joins

# semi_join: customers WHO HAVE an order (no order columns added)
semi_join(customers, orders, by="cust_id")
#   cust_id name    city
# 1       1 Asha   Delhi
# 2       2 Balu  Mumbai
# 3       3 Cena    Pune

# anti_join: customers WHO HAVE NO order
anti_join(customers, orders, by="cust_id")
#   cust_id name    city
# 1       4  Dev Chennai

Joining on Different Column Names

orders2 <- data.frame(
  customer_id = c(1, 2, 3),
  product     = c("Laptop","Phone","Tablet")
)

# Match customers.cust_id to orders2.customer_id
left_join(customers, orders2, by=c("cust_id"="customer_id"))

Joining on Multiple Keys

scores <- data.frame(student_id=c(1,1,2,2), subject=c("Math","Sci","Math","Sci"), score=c(85,90,78,82))
info   <- data.frame(student_id=c(1,1,2,2), subject=c("Math","Sci","Math","Sci"), teacher=c("Mr A","Ms B","Mr A","Ms C"))

left_join(scores, info, by=c("student_id","subject"))

Quick Guide: Which Join to Use?

Need                                     Use
──────────────────────────────────────────────────────────────
Only rows with matches in both tables    inner_join
All rows from left, matched from right   left_join (most common)
All rows from right, matched from left   right_join
All rows from both                       full_join
Check if left rows exist in right        semi_join
Find left rows NOT in right              anti_join

Joins are one of the most fundamental data operations in any analysis — the same concept powers SQL databases, pandas in Python, and dplyr in R. Mastering the four main join types (inner, left, right, full) covers 95% of real-world joining needs.