Skip to main content
Sign in Create a free account
Varsity Tutors
Sign in
Back to lessons

R Programming • R-Specific Topics (Data Workflows)

Joining Data Frames — Join data frames (left_join, inner_join) and interpret row changes (intro)

Master relational join operations in R to combine disparate data sources while understanding how row counts change.

Explore the lesson 16 min read
A statistical scatter plot with code braces and a variable symbol for R programming.
Inside this lesson 10 sections 5 questions 8 figures

SECTION 1

Historical Context & Motivation

The need to combine data from multiple sources is as old as database management itself. In the early days of computing, data lived in flat files, and programmers wrote custom routines to merge records by matching key fields—a tedious and error-prone process. The formalization of relational algebra by Edgar F. Codd in 1970 introduced a principled set of operations—selection, projection, and crucially, the join—that allowed users to declaratively specify how tables should be combined on shared attributes. This theoretical foundation became the backbone of SQL and every relational database system that followed.

When R emerged in the mid-1990s as a statistical programming language, data manipulation was handled primarily through base R functions like merge(). While functional, this approach was verbose and often confusing in its parameter naming. Hadley Wickham's dplyr package, released as part of the tidyverse in 2014, brought a family of expressive join verbs—left_join(), inner_join(), right_join(), and full_join()—that mirror SQL semantics while fitting naturally into R's pipe-based workflow. These functions made relational operations accessible to data scientists who may not have a traditional database background.

1970 • Codd's Relational Model

Edgar F. Codd publishes 'A Relational Model of Data for Large Shared Data Banks,' formalizing join operations as part of relational algebra.

1986 • SQL Standardization

ANSI publishes the first SQL standard, codifying JOIN syntax (INNER JOIN, LEFT OUTER JOIN) as declarative operations on relational tables.

1993 • R Language Created

Ross Ihaka and Robert Gentleman begin developing R at the University of Auckland. Base R includes merge() for combining data frames.

2014 • dplyr Released

Hadley Wickham releases dplyr with a grammar of data manipulation, introducing left_join() and inner_join() as expressive, pipe-friendly join verbs.

2023 • dplyr 1.1+ Enhancements

dplyr introduces join_by() for inequality and rolling joins, expanding the expressiveness of tidyverse join operations beyond simple equality matching.

The central question this lesson addresses is deceptively simple: when you combine two data frames on a shared key, what happens to the number of rows in the result? Understanding row-count changes after a join is critical for data integrity. A misunderstanding of join semantics is one of the most common sources of bugs in data pipelines—accidentally duplicating rows through a one-to-many join or silently dropping observations through an inner join can propagate errors through an entire analysis.

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is essential to establish the conceptual vocabulary that governs all join operations. A join is a binary operation that takes two data frames (or tables) and produces a new data frame by matching rows based on one or more shared columns called key columns. The nature of the match—what happens to unmatched rows, how duplicates are handled—is determined by the type of join you select. In dplyr, the two most commonly used mutating joins are inner_join() and left_join(), each embodying a different philosophy about data preservation.

1

Key Column

The column (or set of columns) shared between both data frames that defines how rows are matched. In dplyr, specified via the by argument. If omitted, dplyr performs a natural join on all columns with matching names.
2

inner_join()

Returns only rows where the key value exists in both data frames. Unmatched rows from either side are discarded. The result row count is at most min(n₁, n₂) for one-to-one joins, but can exceed both for many-to-many.
3

left_join()

Preserves every row from the left data frame (the first argument). If no match exists in the right table, the result contains NA for the right-side columns. The result always has ≥ n₁ rows.
4

Join Cardinality

The relationship between keys: one-to-one (each key appears once per table), one-to-many (key appears once in one table, multiple times in the other), or many-to-many (duplicates in both). Cardinality directly determines row expansion.
5

NA Introduction

When a left join finds no match for a left-table row, R fills the corresponding right-table columns with NA (Not Available). This preserves row count while signaling missing information—a fundamental design choice in R's handling of incomplete data.
✦

KEY TAKEAWAY

Think of an inner join as the intersection of two guest lists at a co-hosted party—only people invited by both hosts get in. A left join is like one host (the left table) insisting that all of their invitees attend regardless, but guests who weren't on the co-host's list simply don't get a party favor (they receive NA instead). This analogy maps directly to database concepts: inner joins filter, left joins preserve.

SECTION 3

Visual Explanation of Join Types

The following diagram illustrates the fundamental difference between inner_join() and left_join() using a concrete example. Two data frames—students and grades—share a key column id. Notice how each join type affects which rows survive and whether NA values appear in the output.

inner_join() vs left_join() — Row Behaviorstudentsidname1Alice2Bob3Carolgradesidgrade1A2B4Cinner_join(students, grades, by="id")idnamegrade1AliceA2BobBCarol (id=3) dropped — no match in gradesid=4 from grades dropped — no match in studentsResult: 2 rows (3 + 3 → 2)left_join(students, grades, by="id")idnamegrade1AliceA2BobB3CarolNAid=4 from grades still dropped — not in left tableResult: 3 rows (all left rows preserved)Key Matching3(Carol)1, 2matched4(no name)students onlybothgrades only
The Venn diagram (bottom-left) shows the key overlap: ids 1 and 2 match, while id 3 exists only in students and id 4 only in grades. The inner join keeps only the intersection (2 rows), while the left join keeps all left-table rows (3 rows), filling unmatched fields with NA.

The visual makes one critical point explicit: an inner_join() can reduce the row count relative to either input table because unmatched rows are discarded from both sides. A left_join() guarantees that you never lose rows from the left table—the result always has at least as many rows as the left input. However, as we will see in Section 5, a left join can actually increase the row count if the right table contains duplicate keys, because each left-table row is replicated for every matching right-table row.

SECTION 4

How Join Row Counts Work

Understanding how the output row count relates to the inputs requires a formal framework. Let L be the left data frame with nL rows and R be the right data frame with nR rows. For a given key value k, let mL(k) be the number of times k appears in L and mR(k) be the number of times it appears in R. The output row count for each join type can be computed by summing contributions over all distinct key values.

INNER JOIN ROW COUNT

ninner=Σk[mL(k)×mR(k)]forallkwheremL(k)>0ANDmR(k)>0n_inner = Σ_k [ m_L(k) × m_R(k) ] for all k where m_L(k) > 0 AND m_R(k) > 0

The sum ranges over key values present in both tables. Each matched key contributes the product of its multiplicities. For one-to-one keys (mL = mR = 1), each key contributes exactly 1 row.

LEFT JOIN ROW COUNT

nleft=Σk[mL(k)×max(mR(k),1)]forallkwheremL(k)>0n_left = Σ_k [ m_L(k) × max(m_R(k), 1) ] for all k where m_L(k) > 0

The sum ranges over key values present in the left table. The max(m_R(k), 1) ensures that even when mR(k) = 0 (no match), the left row is still counted once (with NA fill).

These formulas have important practical implications. In a one-to-one join where each key appears at most once in each table, the inner join produces at most min(nL, nR) rows and the left join produces exactly nL rows. However, in a one-to-many scenario—say, joining a customer table to an orders table where each customer can have multiple orders—both join types produce rows equal to the number of matched order entries, because the customer row is replicated for each associated order.

WORST-CASE (MANY-TO-MANY)

nmax=nL×nR(Cartesianproductwhenallkeysmatch)n_max = n_L × n_R (Cartesian product when all keys match)

If every row in L shares the same key as every row in R, both inner and left joins degenerate into a Cartesian product. This is almost always a bug. dplyr 1.1+ warns about many-to-many relationships by default.

⚠

Defensive Programming

Always check nrow() before and after a join. If the row count increases unexpectedly, you likely have duplicate keys in the right table (for a left join) or both tables (for an inner join). Use dplyr::count(df, key_col) to audit key uniqueness before joining.

SECTION 5

Interpreting Row Changes in Detail

The most common source of confusion for newcomers is predicting the output row count after a join. This section provides a systematic classification of all possible scenarios, illustrated by a second diagram that focuses on the one-to-many case—the situation most likely to surprise you in practice.

One-to-Many left_join(): Row Expansiondepartments (left)dept_iddept_name10CS20Math30Art3 rowsemployees (right)dept_idemp_name10Dara10Eli10Fay20Gail4 rowsleft_join(by="dept_id")Resultdept_iddept_nameemp_namesource10CSDaraCS × Dara10CSEliCS × Eli10CSFayCS × Fay20MathGailMath × Gail30ArtNAno matchResult: 5 rows (left had 3, right had 4 → 5)
Department 10 (CS) has three employees in the right table, so the single CS row from the left table is replicated three times. Department 20 (Math) matches one employee. Department 30 (Art) has no employees, so emp_name is filled with NA. The output (5 rows) exceeds both inputs because of the one-to-many cardinality on key 10.
Scenarioinner_join Row Countleft_join Row CountKey Insight
1:1 — Full overlap= nL = nR= nLBoth joins give identical results
1:1 — Partial overlap< min(nL, nR)= nL (with NAs)Inner loses rows; left preserves them with NA
1:Many — Duplicates in R≥ matched L rows≥ nLRow expansion; each L row replicates per R match
Many:Many — Duplicates in bothUp to nL × nRUp to nL × nRUsually a bug; dplyr warns by default

Row count behavior by join type and key cardinality

SECTION 6

Worked Example: Merging Course Enrollment Data

Suppose you are building a university dashboard and have two data frames: courses (containing course IDs and titles) and enrollments (containing student IDs and the course IDs they are enrolled in). Your goal is to produce a combined table that shows each enrollment alongside the course title, while also identifying courses with zero enrollments.

Joining Courses and Enrollments in R

  1. 1

    Step 1 — Create the data frames

    Define the two tibbles using dplyr-compatible syntax: library(dplyr) courses <- tibble( course_id = c("CS101", "CS201", "CS301"), title = c("Intro to CS", "Data Structures", "Compilers") ) enrollments <- tibble( student_id = c("S1", "S2", "S3", "S4"), course_id = c("CS101", "CS101", "CS201", "CS401") ) Note: courses has 3 rows, enrollments has 4 rows. CS301 has no enrollments. CS401 appears in enrollments but not in courses (perhaps a data-entry error).
    courses: 3 rows × 2 cols; enrollments: 4 rows × 2 cols
  2. 2

    Step 2 — Perform inner_join()

    inner_result <- inner_join(courses, enrollments, by = "course_id") inner_result This matches on course_id. CS101 appears once in courses and twice in enrollments → 1 × 2 = 2 output rows. CS201 appears once in each → 1 × 1 = 1 row. CS301 has no match in enrollments → dropped. CS401 has no match in courses → dropped.
    inner_result: 3 rows (2 for CS101 + 1 for CS201). CS301 and CS401 are lost.
  3. 3

    Step 3 — Perform left_join()

    left_result <- left_join(courses, enrollments, by = "course_id") left_result All 3 courses are preserved. CS101 expands to 2 rows. CS201 matches 1 row. CS301 produces 1 row with student_id = NA. CS401 is still dropped because it only exists in the right table.
    left_result: 4 rows (2 + 1 + 1). CS301 retained with NA; CS401 dropped.
  4. 4

    Step 4 — Verify row counts

    cat("courses:", nrow(courses), "\n") cat("enrollments:", nrow(enrollments), "\n") cat("inner_join:", nrow(inner_result), "\n") cat("left_join:", nrow(left_result), "\n") Output: courses: 3 enrollments: 4 inner_join: 3 left_join: 4 The inner join lost the CS301 course (no enrollment) but gained a row from the CS101 one-to-many expansion. The left join has 4 rows—more than the original 3 courses—because of the CS101 one-to-many match.
    inner_join: 3 rows | left_join: 4 rows
  5. 5

    Step 5 — Identify courses with zero enrollments

    left_result %>% filter(is.na(student_id)) This returns the single row for CS301 (Compilers) with NA in student_id. This is a common pattern: use a left join followed by filter(is.na(...)) to find unmatched records—functionally equivalent to SQL's LEFT JOIN ... WHERE right_key IS NULL anti-join pattern.
    One row: CS301, Compilers, NA — this course has no enrollments.

SECTION 7

Strengths, Limitations & Comparison

Choosing between inner_join() and left_join() is not merely a syntactic preference—it reflects a deliberate decision about data completeness versus data purity. The following table summarizes the trade-offs, and also compares dplyr's join verbs against base R's merge() function.

Criterioninner_join()left_join()merge() (base R)
Rows preservedOnly matched rows from both tablesAll left-table rows; matched right rowsConfigurable via all.x, all.y
NA introductionNever — unmatched rows are droppedYes — unmatched right-side columns filled with NADepends on all.x/all.y settings
Risk of silent data lossHigh — unmatched rows disappear silentlyLow for left table; right-only rows still lostModerate — default drops unmatched from both
Pipe compatibilityExcellent — designed for %>% / |> chainsExcellent — designed for %>% / |> chainsAwkward — positional args don't read well in pipes
Best use caseWhen unmatched data is irrelevant or erroneousWhen left-table completeness is requiredLegacy code; no tidyverse dependency

Comparison of join approaches in R

✦

KEY TAKEAWAY

In production data pipelines, left_join() is often the safer default because it preserves all records from the primary (left) table, making missing data explicit through NA rather than silently dropping rows. Think of it like a compiler warning versus a runtime crash: the NAs flag a potential issue for you to address downstream, whereas an inner join's dropped rows may go unnoticed until the analysis is wrong. Reserve inner_join() for situations where you intentionally want to filter to the intersection of both datasets.

SECTION 8

Connection to Advanced Join Operations

The inner_join() and left_join() operations covered in this lesson are mutating joins—they add columns from one table to another. dplyr also provides filtering joins (semi_join() and anti_join()) that use the same key-matching logic but only filter rows without adding columns. Additionally, right_join() and full_join() extend the preservation logic to the right table or both tables, respectively. Understanding inner and left joins deeply prepares you to reason about any of these variants.

This LessonAdvanced ExtensionKey Difference
inner_join()semi_join()semi_join keeps only left-table columns; acts as a filter, not a merge. Never creates duplicates.
left_join() + filter(is.na(...))anti_join()anti_join directly returns left rows with no match, avoiding the intermediate NA-filled result.
left_join()right_join() / full_join()right_join preserves right-table rows; full_join preserves all rows from both tables (NAs on both sides).
Equality matching (by = "col")join_by() with inequality / rollingdplyr 1.1+ supports non-equi joins: closest(), between(), and comparison operators like >= inside join_by().

From introductory to advanced join operations

As you move into more complex data engineering tasks—building ETL pipelines, working with normalized database schemas, or performing feature engineering for machine learning—you will encounter all of these join types regularly. The row-change reasoning you developed in this lesson (checking cardinality, predicting output size, auditing for unexpected expansion) applies universally, whether you are working in R, SQL, pandas, or Spark.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain in your own words why a left_join() can produce NA values in the result but an inner_join() cannot. Under what circumstances would both functions return identical results?
Show answer
A left join preserves every row from the left table regardless of whether a matching key exists in the right table. When no match exists, the columns originating from the right table have no values to fill, so R inserts NA. An inner join, by contrast, only retains rows where the key is present in both tables, so there is always a right-side value to fill in—no NAs are needed. The two functions return identical results when every key in the left table has at least one corresponding match in the right table (i.e., the left table's key set is a subset of the right table's key set) and the relationship is one-to-one.

Question 2: BASIC CALCULATION

Data frame A has 5 rows with unique key values {1, 2, 3, 4, 5}. Data frame B has 3 rows with unique key values {2, 4, 6}. How many rows will inner_join(A, B) produce? How many will left_join(A, B) produce?
Show answer
The matching keys are {2, 4}—two values present in both tables. Since all keys are unique (one-to-one), inner_join(A, B) produces exactly 2 rows. For left_join(A, B), all 5 rows from A are preserved. Keys 1, 3, and 5 have no match in B, so their B-columns are filled with NA. The result has 5 rows: 2 fully matched and 3 with NAs in the B-side columns. Key 6 from B does not appear in the result of either join because it is absent from the left table.

Question 3: INTERMEDIATE

You have products (100 rows, unique product_id) and reviews (500 rows; each product can have 0 to many reviews). You run left_join(products, reviews, by = "product_id") and get 480 rows. (a) Is this possible? (b) How many products have zero reviews? (c) What is the average number of reviews per reviewed product?
Show answer
(a) Yes, this is possible. The left join preserves all 100 products, but products with multiple reviews contribute multiple rows. (b) Let p₀ be the number of zero-review products. Each such product contributes exactly 1 row (with NA). The remaining (100 − p₀) products contribute rows equal to their review count. Total reviews from matched products = 480 − p₀ (the NA rows don't correspond to actual reviews). Since there are 500 reviews total and only 480 − p₀ appear in the result, we need 480 − p₀ = 500 − (reviews for products not in the 100), but all reviews reference product_ids that must be in products for them to match. Actually, some reviews may reference products not in our table. If we assume all 500 reviews match valid products, then 480 − p₀ = 500 is impossible. So some reviews reference invalid product_ids. If all reviews do match: 480 = (100 − p₀) × avg + p₀. Simplifying: reviews matched = 480 − p₀, so 480 − p₀ ≤ 500. If 480 − p₀ = matched reviews from the 500, and 20 + p₀ reviews reference products outside the 100. With p₀ = 20: 480 − 20 = 460 matched reviews, meaning 40 reviews reference missing products. (c) Average reviews per reviewed product = 460 / 80 = 5.75.

Question 4: APPLIED

You are building an analytics pipeline. You have users (user_id, signup_date) and sessions (user_id, session_start, duration). Write R code using dplyr to: (1) join the tables so that every user appears even if they have no sessions, (2) count the number of sessions per user, and (3) filter to users with zero sessions. Explain your choice of join type.
Show answer
Use left_join() because you want all users preserved. Code: library(dplyr) result <- users %>% left_join(sessions, by = "user_id") %>% group_by(user_id, signup_date) %>% summarise(n_sessions = sum(!is.na(session_start)), .groups = "drop") zero_session_users <- result %>% filter(n_sessions == 0) The left join ensures users with no sessions appear with NA in session columns. In the summarise step, sum(!is.na(session_start)) counts non-NA sessions, correctly giving 0 for users with no sessions. An inner_join would silently exclude these users, making the zero-session analysis impossible.

Question 5: CRITICAL THINKING

A colleague runs left_join(orders, customers, by = "customer_id") where orders has 10,000 rows and customers has 5,000 rows. The result has 10,000 rows and they conclude: 'The join worked correctly—same number of rows.' Critically analyze this conclusion. What assumptions must hold for this to be correct? What could be hiding beneath the surface?
Show answer
The colleague's conclusion is superficially reasonable but insufficiently rigorous. The output having exactly 10,000 rows is consistent with a correct one-to-one left join, but it does not guarantee correctness. Several issues could be hiding. First, some order rows may have customer_ids not present in the customers table, resulting in NA-filled customer fields—the row count would still be 10,000 but with missing data. Second, the row count staying at 10,000 only tells us that the net effect is neutral, but it's possible that some orders matched multiple customer rows (duplicates in customers) while other orders lost matches, and the expansion and NA-addition coincidentally sum to 10,000. To properly validate, one should: (1) check sum(is.na(result$customer_name)) to count unmatched orders; (2) verify n_distinct(customers$customer_id) == nrow(customers) to confirm key uniqueness; and (3) compare nrow(inner_join(orders, customers)) against the left join result to quantify how many rows survive the stricter join. Row-count equality alone is a necessary but not sufficient condition for join correctness.

SUMMARY

Lesson Summary

Summary

This lesson introduced the two most commonly used mutating join operations in dplyr. An inner_join() returns only rows with matching key values in both data frames, effectively computing their intersection and potentially reducing the row count. A left_join() preserves every row from the left data frame, filling unmatched right-side columns with NA, which makes missing data explicit rather than silent.

The critical skill developed here is interpreting row changes after a join. The output row count depends on join cardinality: one-to-one joins preserve or reduce rows, while one-to-many joins can expand them as left-table rows are replicated. Always verify row counts with nrow() and audit key uniqueness before joining. These foundational skills transfer directly to SQL, pandas, and any other framework built on relational algebra.

Varsity Tutors • R Programming • Joining Data Frames — Join data frames (left_join, inner_join) and interpret row changes (intro)