Skip to main content
v2026.11,986 entries · CC-BY 4.0

Merge Function in R: How to Join Data Frames

Learn how to use the merge function in R: perform inner, outer, left, and right joins, manage duplicate keys, and prevent data loss in research datasets.

Written and maintained by CASRAI Editorial Board

Published · Last updated

On this page

The merge() function in R combines two data frames or matrices by matching common key columns, row names, or executing relational database join operations. Built into base R as an S3 generic method (merge.data.frame), it enables researchers to integrate separate experimental measurements, demographic records, survey waves, and assay results without installing external packages. By default, merge() performs a natural inner join, matching rows on all common column names and returning only intersecting observations, while parameter flags enable left, right, and full outer joins, custom suffix naming, and Cartesian products.

What It Is Used for in Research

Tabular data integration is a fundamental requirement across empirical scientific disciplines. In biomedical science, clinical trials, epidemiology, and economics, primary data rarely originate in a single spreadsheet. Instead, research data are partitioned across multiple instruments, experimental protocols, and administrative registries to preserve normalization and adhere to FAIR data principles. Researchers routinely employ merge() for several core tasks:

  • Linking Clinical Registries and Laboratory Assays: Combining master patient demographic files (participant ID, age, sex, cohort) with longitudinal blood chemistry, biomarker concentrations, or genomic sequencing results.
  • Consolidating Longitudinal Study Waves: Aligning repeated measures across multiple survey waves or clinical visits, matching records using composite keys such as participant ID and visit number.
  • Integrating Survey Weights and Geographic Classifications: Attaching census-tract socioeconomic indices, FIPS codes, or complex design weights (such as those analyzed with the survey R package) to individual participant responses.
  • Reconciling Multi-Center Trial Data: Merging site-level institutional characteristics with participant-level clinical outcome tables across multi-institution collaborative consortia.
  • Harmonizing Normalized Relational Schemas: Converting relational laboratory information management systems (LIMS) exports into flat analysis-ready data frames for regression modeling, survival analysis, or publication tables.

Because base R is installed on every computing cluster and secure virtual desktop, merge() serves as the baseline, dependency-free reference tool for reproducible data preparation. When establishing institutional data pipelines, ensuring proper relational joins is an essential aspect of robust data administration, bridging data management vs data governance frameworks.

How to install or access it

The merge() function is an integral component of the base R system. It is included in the core base package, which is loaded automatically upon launching an R session. No third-party packages or compilation steps are required.

To access and run merge() in a verified research environment:

  1. Obtain R from CRAN: Download and install R from the Comprehensive R Archive Network (CRAN) at cran.r-project.org, or use an institutional distribution configured by your computing administrator.
  2. Select an Interactive Interface: Access R through an interactive terminal, the official R GUI, RStudio Desktop, or an editor configured with the official R language extension, such as VS Code with the R extension.
  3. Verify Built-in Function Availability: In your active R console, confirm that the base generic and method are directly accessible by typing:
    is.function(merge)
    help("merge.data.frame", package = "base")
  4. Test on Standard Datasets: You can practice merging operations immediately using standard datasets built into base R; for guidance on discovering and loading built-in research data, consult our guide on datasets in R packages.

Step-by-Step Task: Merging Research Data Frames

To demonstrate standard data merging workflows, consider a common clinical research scenario: linking a baseline clinical trial registry containing participant demographics with a longitudinal laboratory report containing biomarker concentrations measured across multiple clinical follow-up intervals.

Begin by constructing two reproducible research data frames in your R session:

# Baseline patient demographic registry
df_baseline <- data.frame(
  patient_id = c("P-101", "P-102", "P-103", "P-104", "P-105"),
  age_years = c(54, 62, 48, 71, 59),
  treatment_arm = c("Control", "Drug_A", "Drug_B", "Drug_A", "Control"),
  stringsAsFactors = FALSE
)

# Longitudinal laboratory biomarker measurements
df_biomarkers <- data.frame(
  patient_id = c("P-102", "P-103", "P-104", "P-106"),
  visit_month = c(3, 6, 3, 12),
  crp_mg_l = c(2.4, 0.8, 5.1, 3.2),
  stringsAsFactors = FALSE
)

Step 1: Inspect Key Columns and Data Types

Before executing any join, verify that matching key columns share identical names, data structures, and casing across both data frames. Discrepancies between character strings and factors represent frequent causes of failed joins.

# Check column names and structures
names(df_baseline)
names(df_biomarkers)
str(df_baseline$patient_id)
str(df_biomarkers$patient_id)

Step 2: Execute an Inner Join (Natural Join)

The default call to merge() executes an inner join. When all = FALSE (the default), the function extracts and returns only rows where the key identifier exists in both datasets. Observations present in only one table are excluded.

# Inner join on patient_id
res_inner <- merge(df_baseline, df_biomarkers, by = "patient_id")
print(res_inner)

Participants P-101 and P-105 are dropped because they lack laboratory records, while participant P-106 is dropped because they do not appear in baseline enrollment. The result contains three matching rows (P-102, P-103, and P-104).

Step 3: Execute a Left Outer Join

In cohort studies, researchers often require that all baseline enrolled participants remain in the analytical dataset, even if follow-up samples were missed. Setting all.x = TRUE executes a left outer join, retaining every row from x and filling missing values from y with NA.

# Left outer join: retain all baseline subjects
res_left <- merge(df_baseline, df_biomarkers, by = "patient_id", all.x = TRUE)
print(res_left)

All five baseline patients (P-101 through P-105) are preserved. Unmatched patients P-101 and P-105 display NA for visit_month and crp_mg_l.

Step 4: Execute a Right Outer Join

A right outer join retains all observations from the second data frame (y), matching available attributes from x and inserting NA values where the left table lacks a matching key. This operation is invoked with all.y = TRUE.

# Right outer join: retain all biomarker records
res_right <- merge(df_baseline, df_biomarkers, by = "patient_id", all.y = TRUE)
print(res_right)

Participant P-106 is retained in the output, with baseline attributes age_years and treatment_arm populated with NA.

Step 5: Execute a Full Outer Join

To retain every observation from both datasets without discarding unmatched rows from either source, specify all = TRUE (which sets both all.x = TRUE and all.y = TRUE).

# Full outer join: retain all records from both datasets
res_full <- merge(df_baseline, df_biomarkers, by = "patient_id", all = TRUE)
print(res_full)

The resulting data frame contains six distinct participant records (P-101 through P-106), with NA placeholders wherever data are absent in either contributing table.

Step 6: Merge Datasets with Differing Key Column Names

When data frames use different column labels for the same entity (such as subject_code in an EHR export versus pt_id in a laboratory database), specify by.x and by.y independently:

# Data frames with mismatched identifier column names
df_demo <- data.frame(subject_code = c("S1", "S2"), age = c(45, 52))
df_clin <- data.frame(pt_id = c("S1", "S2"), sys_bp = c(120, 135))

# Merge using disparate key column specifications
res_diff_keys <- merge(df_demo, df_clin, by.x = "subject_code", by.y = "pt_id")
print(res_diff_keys)

The resulting data frame adopts the column name from by.x (here, subject_code) as the key column in the merged output, omitting by.y to prevent redundant identifier duplication.

Step 7: Merge on Multiple Composite Keys

When records are indexed by multi-variable keys—such as a clinical study tracking subjects across repeated visits—pass a character vector of column names to the by parameter:

# Multi-key clinical records
df_vitals <- data.frame(
  subject_id = c("S1", "S1", "S2", "S2"),
  visit_num = c(1, 2, 1, 2),
  pulse_bpm = c(72, 76, 80, 84)
)

df_labs <- data.frame(
  subject_id = c("S1", "S1", "S2", "S3"),
  visit_num = c(1, 2, 1, 1),
  glucose_mg_dl = c(95, 102, 110, 88)
)

# Merge on composite primary key
res_composite <- merge(df_vitals, df_labs, by = c("subject_id", "visit_num"))
print(res_composite)

Step 8: Disambiguate Non-Key Column Collisions

If both data frames contain columns with identical names not included in the by specification, merge() automatically resolves the collision by appending suffixes. By default, suffixes = c(".x", ".y") is applied. In published research code, replacing these defaults with descriptive suffixes improves code clarity:

# Disambiguate with explicit, meaningful suffixes
res_suffixed <- merge(
  df_baseline, 
  df_biomarkers, 
  by = "patient_id", 
  suffixes = c("_baseline", "_followup")
)

Furthermore, base R enforces no.dups = TRUE by default (standardized in R version 3.5.0). When no.dups = TRUE, R checks if any column name in by.x matches an unmerged column name in y, appending suffixes to ensure every column in the returned data frame possesses a strictly unique name.

Step 9: Merge Using Row Names

In bioinformatic pipelines—such as microarray or RNA-sequencing expression matrices where gene symbols or sample identifiers are stored as row names—you can merge directly on row names by setting by = "row.names" or by = 0:

# Expression matrices indexed by gene symbols
mat_assay1 <- data.frame(
  sample_A = c(10.2, 8.4, 12.1), 
  row.names = c("BRCA1", "TP53", "EGFR")
)
mat_assay2 <- data.frame(
  sample_B = c(9.8, 11.5, 7.9), 
  row.names = c("TP53", "EGFR", "MYC")
)

# Merge on row names
res_rownames <- merge(mat_assay1, mat_assay2, by = "row.names", all = TRUE)
print(res_rownames)

When merging on row names, base R creates a new character column on the far left titled Row.names containing the matching indices, while the output data frame itself receives automatic sequential integer row names.

Output and How to Read It

Understanding how base R organizes the merged output object is essential for interpreting downstream statistical models and writing reproducible reporting scripts:

  • Column Structure and Ordering: The output of merge() places common matching key columns (specified in by) in the leftmost positions. These are followed by all remaining unmerged columns from the first dataset (x), and finally all remaining unmerged columns from the second dataset (y). If row names were used for matching, an explicit Row.names column occupies the first position.
  • Row Sorting Behavior: By default, merge() executes with sort = TRUE, sorting output rows lexicographically according to the values in the by columns. If preserving the original physical row ordering of dataset x is required, specify sort = FALSE. Note that even with sort = FALSE, row order is not guaranteed to strictly match x if unmatched or multi-matched rows occur.
  • Row Names Replacement: The resulting data frame always receives automatic sequential integer row names (1:nrow(result)). Any custom row names originally present on the input data frames x and y are discarded unless explicitly converted into a data column prior to the join or matched via by = 0.
  • Missing Value Representations: Unmatched rows resulting from left, right, or full outer joins receive standard R missing value indicators (NA) across all columns originating from the missing table. These missing values must be handled appropriately in subsequent modeling steps (e.g., using complete.cases() or na.omit()).
  • Publication-Quality Formatting: In research publications, merged data frames are often finalized into formal summary tables; for formatting tables with custom headers, borders, and statistical formatting, see our guide on flextable.

Troubleshooting

Data frame merging frequently exposes subtle structural anomalies in empirical research datasets. The following diagnostic checks resolve the most common issues encountered when running merge():

Unintended Row Multiplication from Duplicate Keys

The most dangerous pitfall in relational data joins is unintended row multiplication caused by many-to-many relationships. If an identifier appears multiple times in x and multiple times in y, merge() silently computes the Cartesian product for that key, generating M × N rows without issuing a warning or error. In epidemiological cohorts, this can severely inflate sample size, distort regression coefficients, and violate independence assumptions.

To detect and prevent duplicate key expansion, verify key uniqueness before merging:

# Check for duplicate keys prior to merging
any(duplicated(df_baseline$patient_id))
any(duplicated(df_biomarkers$patient_id))

# Verify output row counts against expectations
nrow_before <- nrow(df_baseline)
res <- merge(df_baseline, df_biomarkers, by = "patient_id", all.x = TRUE)
if (nrow(res) > nrow_before) {
  warning("Merge generated unexpected row inflation: check for duplicate keys in y!")
}

Missing Rows from Data Type Mismatches

If merge() returns zero rows or drops expected matches despite identical visible text in both tables, inspect the underlying storage mode of the key columns. In older R scripts or datasets imported via read.csv() without stringsAsFactors = FALSE, key columns may be encoded as factors with differing factor levels, or numerical IDs may have been parsed as characters in one file and integers in another.

# Harmonize key column types explicitly before merging
df_baseline$patient_id <- as.character(df_baseline$patient_id)
df_biomarkers$patient_id <- as.character(df_biomarkers$patient_id)

Loss of Custom Row Names

Because merge() always resets row names to sequential integers, researchers working with genomic expression matrices often discover that their sample or gene row names have vanished. To preserve row names as permanent data attributes, explicitly migrate them into a standard data column before running merge():

# Preserve row names as a dedicated column
mat_assay1$gene_symbol <- rownames(mat_assay1)
mat_assay2$gene_symbol <- rownames(mat_assay2)
merged_assay <- merge(mat_assay1, mat_assay2, by = "gene_symbol", all = TRUE)

Missing Value Matching and the Incomparables Argument

In standard ANSI SQL database operations, NULL values never match one another. In base R, however, merge() matches NA to NA by default, potentially linking distinct research subjects whose identifiers were unrecorded. To prevent NA keys from matching across datasets, supply incomparables = NA:

# Prevent NA values in key columns from joining with each other
res_safe <- merge(df_x, df_y, by = "patient_id", incomparables = NA)

Memory Overhead and Vector Index Limits

Base R merge() operates entirely in system memory (RAM) and relies on base R indexing vectors. R restricts standard vector indices to less than 2^31 elements (approximately 2.14 billion rows), and long vectors are not accepted as inputs for merge.data.frame. For massive genomic, sensor, or electronic health record datasets containing tens of millions of rows, base merge() can experience severe performance degradation or trigger vector memory allocation limits. In such scenarios, researchers should transition to keyed indexing tools such as data.table or out-of-core database engines.

Free Alternatives

While base R merge() requires no external libraries and remains universally accessible, modern R ecosystems provide several specialized packages designed for enhanced syntax, higher computational speed, or out-of-core memory handling.

Package / Tool Primary Join Syntax Key Advantages Primary Use Case in Research
Base R (merge) merge(x, y, by = ...) Zero dependencies; built into core R; stable syntax across decades of R releases. Standard scripts, educational environments, and minimal-dependency production pipelines.
dplyr (tidyverse) left_join(x, y, by = ...) Explicit semantic functions; pipe-friendly (|>); warns on unintended many-to-many matches. Interactive data exploration, reproducible data pipelines, and tidy research workflows.
data.table x[y, on = .(key)] or merge(dt1, dt2) Ultra-fast C-level binary search; minimal memory copying; supports secondary indexing. High-throughput genomics, large administrative claims databases, and memory-constrained servers.
sqldf sqldf("SELECT * FROM x JOIN y...") Native SQL ANSI syntax; transparent SQLite/PostgreSQL backend integration. Researchers with existing SQL training who prefer relational database syntax.
DuckDB (duckdb) dbGetQuery(con, "SELECT...") Out-of-core columnar execution; queries files larger than physical RAM without memory crashes. Large-scale observational cohorts, biobank datasets, and multi-gigabyte parquet archives.

When selecting an alternative, consider the volume of your research data. For small to moderately sized datasets (fewer than 500,000 rows), base R merge() and dplyr::left_join() perform reliably with negligible differences in execution time. For datasets exceeding several million rows, data.table and DuckDB offer significant speed advantages through optimized binary searching and columnar execution.

The interactive dataset table below compares technical specifications, join mechanisms, sorting rules, and memory characteristics across these tools in comprehensive detail.

Frequently Asked Questions (FAQ)

How does the merge function in R differ from SQL joins?

The merge() function provides direct equivalents to standard relational SQL joins through its logical arguments: all = FALSE executes an inner join (natural join), all.x = TRUE executes a left outer join, all.y = TRUE executes a right outer join, and all = TRUE executes a full outer join. However, unlike standard SQL engines where NULL values never match, base R’s merge() matches NA to NA by default unless incomparables = NA is explicitly specified.

Why did merge() duplicate rows in my resulting data frame?

Row duplication occurs when the key columns specified in by contain non-unique (duplicate) values in both datasets. When a key value appears M times in the first data frame and N times in the second, merge() calculates all possible combinations, producing M × N output rows for that identifier. To prevent this, confirm that at least one of the contributing data frames has strictly unique key values using any(duplicated(df$key)) prior to joining.

How can I merge more than two data frames simultaneously in R?

Base R’s merge() accepts exactly two data frames (x and y) per call. To merge three or more research data frames by a common key without repetitive code, use the base R Reduce() higher-order function:

# Merge a list of multiple data frames on patient_id
df_list <- list(df_demographics, df_vitals, df_labs, df_outcomes)
combined_df <- Reduce(function(x, y) merge(x, y, by = "patient_id", all = TRUE), df_list)

Does merge() preserve existing row names in R data frames?

No. When merge() executes, it discards custom row names previously assigned to data frames x and y, resetting output row names to sequential integer strings (1:nrow). If row names carry essential metadata (such as gene identifiers or specimen barcodes), copy them into an explicit data column using df$id <- rownames(df) before merging, or join directly on row names with by = "row.names".

How do I perform a cross join (Cartesian product) using merge()?

To compute a complete Cartesian product—matching every row of x with every row of y without filtering on any key column—pass by = NULL or a zero-length character vector (by = character(0)). The resulting data frame has dimensions equal to nrow(x) * nrow(y) rows and ncol(x) + ncol(y) columns.

When should I switch from base merge() to data.table or dplyr?

Base R merge() is ideal for standalone research scripts, self-contained replication packages, and datasets with fewer than several hundred thousand rows where external dependencies must be avoided. Researchers should switch to dplyr when building interactive exploratory workflows that benefit from the tidyverse pipe and explicit join warnings. For datasets exceeding several million rows, or in memory-constrained HPC environments, data.table provides substantially faster execution times and lower memory usage through in-place binary searching.

Sources

Data: R Data Merging and Join Methods Comparison Matrix

Systematic comparison of tabular data joining mechanisms across base R, dplyr, data.table, sqldf, and duckdb, documenting syntax, default sorting, key matching rules, and memory behavior compiled from official package documentation and CRAN manuals.

Period covered: 2024/2026. Coverage: Global.

R Data Merging and Join Methods Comparison Matrix
Tool / ImplementationPrimary Function or SyntaxDefault Join TypeDefault Row OrderingDuplicate Key HandlingMemory and Indexing ModelRow Capacity Limit
Base R (merge.data.frame)merge(x, y, by = ...)Inner join (natural join)Sorted lexicographically by keys (sort = TRUE)Silent Cartesian product expansionCopies data frames into RAM vectorsRestricted to < 2^31 rows per vector
tidyverse (dplyr)left_join(x, y, by = ...)Left outer join (dedicated function per join type)Preserves row order of first table (x)Throws warning unless relationship specifiedAltered vector evaluation in RAMSubject to available system RAM
data.tablex[y, on = .(key)] or merge(x, y)Right join for x[y]; Inner join for merge()Preserves key order or row order of second tableCartesian product with allow.cartesian checkC-level binary search with secondary indexing64-bit integer indexing (> 2^31 rows supported)
sqldfsqldf('SELECT * FROM x JOIN y...')Inner join (or explicit SQL JOIN clause)Preserves database table order unless ORDER BY usedStandard SQL Cartesian expansionCopies data frames into embedded SQLite/H2 in RAMBound by SQLite database and table limits
DuckDB (duckdb)dbGetQuery(con, 'SELECT...')Inner join (or explicit SQL JOIN clause)Preserves query pipeline order unless ORDER BY usedStandard SQL Cartesian expansionVectorized columnar execution engine on disk/RAMHandles out-of-core larger-than-RAM datasets

Sources

Compiled by CASRAI from the sources above. Free to reuse under CC BY 4.0 with attribution.

Follow CASRAI

Research-administration guidance, standards updates and independent tool reviews.

Ask CASRAI · full access

Ask about Merge Function in R: How to Join Data Frames

Full access: large monthly usage, up to 10 source passages behind each answer, and MCP and API access for Claude Code, Codex and other CLIs. Cancel any time.

An AI assistant for research: funder rules, compliance, methods and publishing. It links the sources behind its answers and labels answers that come from a web search.

Answers draw on CASRAI's guides and dictionary plus the federal and funder documents we index: Federal Register, Grants.gov, Regulations.gov and UKRI.

Works on this site and inside Claude, Cursor and the AI tools you already use.

Everything CASRAI publishes — this page, the dictionary, the guides and the news — stays open to read, with no account and no card.

Referenced across the research world

University of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logoUniversity of Cambridge logoColumbia University logoCrossref logoUniversity of Edinburgh logoHarvard University logoUniversity of Oxford logoPrinceton University logoStanford School of Medicine logoUniversity College London logoORCID logo
  • University of Cambridge logo
  • Columbia University logo
  • Crossref logo
  • University of Edinburgh logo
  • Harvard University logo
  • University of Oxford logo
  • Princeton University logo
  • Stanford School of Medicine logo
  • University College London logo
  • ORCID logo

View CASRAI adoption →