Joining Data

Reviewed & published by Brayan K

A join combines two tables by matching rows on a shared key column — the way you bring related data, split across separate tables, back together for analysis.

Part of the free R course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

By the end of this lesson you'll use inner_join() and left_join() to combine tables, understand full_join() and anti_join(), and chain a join into a grouped summary.

What You'll Learn in This Lesson

• Why related data is split across tables
• How inner_join() keeps only matched rows
• How left_join() keeps all left-table rows
• How full_join() keeps everything from both
• How anti_join() finds non-matching rows
• How to specify the key with the by argument

1️⃣ inner_join(): Only Matches

inner_join(a, b, by = "key") keeps only rows whose key appears in both tables. Rows without a partner on either side are dropped.

library(dplyr)

# Two tables sharing a key column "id".
customers <- data.frame(
  id = c(1, 2, 3),
  name = c("Ada", "Bob", "Cara")
)
orders <- data.frame(
  id = c(1, 1, 2, 4),
  item = c("pen", "pad", "ink", "mug")
)

# inner_join keeps only rows with a match in BOTH tables (id 1 and 2).
inner_join(customers, orders, by = "id")
#   id name item
# 1  1  Ada  pen
# 2  1  Ada  pad
# 3  2  Bob  ink

2️⃣ left_join(): Keep the Left Table

left_join() keeps every row of the left table and attaches matching data from the right, filling NA where there's no match. It's the most common join because it never loses your main records.

library(dplyr)

customers <- data.frame(
  id = c(1, 2, 3),
  name = c("Ada", "Bob", "Cara")
)
orders <- data.frame(
  id = c(1, 1, 2),
  item = c("pen", "pad", "ink")
)

# left_join keeps ALL customers; non-matching ones get NA for order columns.
left_join(customers, orders, by = "id")
#   id name item
# 1  1  Ada  pen
# 2  1  Ada  pad
# 3  2  Bob  ink
# 4  3 Cara <NA>   <- Cara has no orders

3️⃣ full_join() and anti_join()

full_join() keeps all rows from both tables; anti_join() is a filtering join that returns rows of the first table with no match in the second — ideal for "what's missing" questions.

library(dplyr)

a <- data.frame(id = c(1, 2), x = c("a1", "a2"))
b <- data.frame(id = c(2, 3), y = c("b2", "b3"))

# full_join keeps EVERYTHING from both, filling gaps with NA.
full_join(a, b, by = "id")
#   id    x    y
# 1  1   a1 <NA>
# 2  2   a2   b2
# 3  3 <NA>   b3

# anti_join keeps rows of 'a' with NO match in 'b' (great for "what's missing").
anti_join(a, b, by = "id")     # -> only id 1

Your turn. Fill in the ___ blank, run it, and compare with the expected output.

library(dplyr)

# 🎯 YOUR TURN — replace each ___ then run it.

products <- data.frame(
  sku = c("A", "B", "C"),
  name = c("Pen", "Pad", "Ink")
)
sales <- data.frame(
  sku = c("A", "A", "C"),
  qty = c(3, 2, 5)
)

# Keep every product, attaching sales where they exist (Pad will have NA qty).
joined <- ___(products, sales, by = "sku")   # 👉 replace ___ with left_join

print(joined)

# ✅ Expected output:
#   sku name qty
# 1   A  Pen   3
# 2   A  Pen   2
# 3   B  Pad  NA
# 4   C  Ink   5

Mini-Challenge: Revenue per Customer

Write the full pipeline from the outline, run it, and check it against the example output. Join then group then summarise is one of the most common real analyses.

# 🎯 MINI-CHALLENGE: revenue per customer.
#
# Tables:
#   customers <- data.frame(id = c(1,2,3), name = c("Mia","Leo","Zoe"))
#   orders    <- data.frame(id = c(1,1,2), amount = c(100, 50, 200))
#
# In one pipeline:
# 1. inner_join customers and orders by "id".
# 2. group_by(name).
# 3. summarise(total = sum(amount)).
# 4. arrange() by total, highest first.
#
# ✅ Example output (a tibble):
#   name  total
# 1 Mia     150
# 2 Leo     200  -> after arrange, Leo (200) is first

# your code here

Common Errors (and the fix)

• Rows mysteriously disappear — an inner_join dropped unmatched rows. Use left_join to keep your main table.
• Key columns named differently — match them with by = c("custId" = "id").
• Row count explodes — duplicate keys on both sides multiply rows (a many-to-many join). Check for duplicates with count() first.
• Suffixes like .x / .y — both tables had a non-key column with the same name. Rename or set suffix =.

Pro Tips

• 💡 Default to left_join: it preserves your primary table, which is usually what you want.
• 💡 Check before joining: confirm key uniqueness with count(df, key) |> filter(n > 1).
• 💡 semi_join for filtering: keep only rows that have a match without adding columns — handy for "active users only".

📋 Quick Reference — Joins

🎉 Lesson Complete!

• ✅ A join combines tables on a shared key
• ✅ inner_join() keeps only matched rows
• ✅ left_join() keeps all left-table rows
• ✅ full_join() keeps everything; anti_join() finds gaps
• ✅ Set the key with by = "id"

Practice quiz

Continue this course

• Previous: group_by & summarise
• Next: Statistical Functions — Means, distributions, correlation, and hypothesis tests in base R
• Quick reference: R cheat sheet

Frequently asked questions

What is a join and why do I need one?

A join combines two tables by matching rows on a shared key column — like attaching customer names to an orders table using a customer id. Real data is usually split across multiple tables to avoid repetition (one customers table, one orders table), and joins put the pieces back together for analysis. dplyr provides inner_join(), left_join(), right_join(), and full_join(), each differing in which non-matching rows they keep.

What's the difference between inner_join and left_join?

inner_join() keeps only the rows that have a match in BOTH tables — anything without a partner is dropped. left_join() keeps ALL rows from the left (first) table, attaching matching data from the right where it exists and filling NA where it doesn't. Use inner_join when you only care about matched records, and left_join when you must preserve every row of your main table — for example keeping all customers even those with no orders.

How does R know which columns to join on?

You tell it with the by argument: by = "id" matches rows where the id column is equal in both tables. If the key columns have different names, use by = c("left_name" = "right_name"). If you omit by, dplyr does a 'natural join' on all columns the two tables share and prints a message saying which ones — convenient, but being explicit with by avoids surprises and makes your code clearer.

What does anti_join do, and when is it useful?

anti_join(a, b) returns the rows of table a that have NO match in table b — it's a filtering join, not a combining one, so it doesn't add columns from b. It's perfect for 'what's missing' questions: which customers have never placed an order, which products were never sold, which users in one list aren't in another. Its companion semi_join() does the opposite, keeping only the rows of a that DO have a match.