Quick summary
Summarize this blog with AI
Introduction
A left join retains every left-side row, but it does not guarantee the same row count. Each matching right-side row produces an output row. Use the expected key relationship, missing-key policy and output grain to distinguish legitimate expansion from a join bug.
Tested with R 4.5.1 and dplyr 1.1.4. Run the code blocks in order. Install the named packages before starting; inputs are supplied below.
A complete customer/order fixture
Customer IDs are labels, so leading zeros are meaningful. The customer table has one row per customer; the order table has one row per order. Customer 001 has two orders, customers 002 and 003 have none in this extract, and order 104 refers to a customer outside the extract.
customers <- data.frame(customer_id = c("001", "002", "003"),
name = c("Ada", "Ben", "Cy"), left_row = 1:3)
orders <- data.frame(order_id = c(101L, 102L, 104L),
customer_id = c("001", "001", "004"), amount = c(10, 20, 40))
stopifnot(!anyNA(customers$customer_id), !anyDuplicated(customers$customer_id),
!anyDuplicated(orders$order_id))
base_join <- merge(customers, orders, by = "customer_id", all.x = TRUE,
incomparables = NA, sort = FALSE)
base_join <- base_join[order(base_join$left_row, base_join$order_id), ]
rownames(base_join) <- NULL
print(base_join)
stopifnot(nrow(base_join) == 4, identical(base_join$customer_id, c("001", "001", "002", "003")))
The output has 4 rows: two for 001 and one unmatched row each for 002 and 003. merge() defaults to key sorting; sort = FALSE leaves order unspecified, so a saved left-row position and a right-side tie-breaker restore an explicit order. incomparables = NA prevents NA-key matching for this single-key merge.
Declare the expected one-to-many relationship
With dplyr joins, relationship = "one-to-many" asserts that each right row matches at most one left row. It permits a customer to have many orders. na_matches = "never" makes missing IDs nonmatching instead of using the default NA-to-NA matching behavior.
library(dplyr)
joined <- left_join(customers, orders, by = "customer_id",
relationship = "one-to-many", na_matches = "never") |>
arrange(left_row, order_id)
print(joined)
stopifnot(isTRUE(all.equal(as.data.frame(joined), base_join)))
no_orders <- anti_join(customers, orders, by = "customer_id", na_matches = "never")
orphan_orders <- anti_join(orders, customers, by = "customer_id", na_matches = "never")
stopifnot(identical(no_orders$customer_id, c("002", "003")),
identical(orphan_orders$order_id, 104L))
anti_join() diagnoses nonmatches in either direction. “No matching order in this extract” is more precise than “never ordered.” An NA amount in a matched order is also different from a missing right-side order; use a nonmissing order ID or a match marker to tell them apart.
Aggregate first when the desired grain is one row per customer
Do not discard repeated orders with distinct(customer_id, .keep_all = TRUE). If the question is customer order counts, aggregate the order table deliberately before joining. Zero is justified here only for an absent order-count row in the supplied extract.
order_counts <- orders |> count(customer_id, name = "orders_in_extract")
customer_summary <- left_join(customers, order_counts, by = "customer_id",
relationship = "one-to-one", na_matches = "never") |>
mutate(orders_in_extract = coalesce(orders_in_extract, 0L))
print(customer_summary[c("customer_id", "orders_in_extract")])
stopifnot(identical(customer_summary$orders_in_extract, c(2L, 0L, 0L)),
nrow(customer_summary) == nrow(customers))
The counts are 2,0,0. This intentionally changes the right-side grain while preserving all original order records. Revenue aggregation would need a separate policy for unknown amounts, refunds and currency.
Check keys without destructive coercion
If columns have different names, supply an explicit mapping. For a multi-part key, join on every required component. Do not convert IDs to integer merely to make types agree: "001" and "1" may denote different entities. Whitespace/case normalization likewise requires a domain rule and a collision check.
lookup <- data.frame(account = c("001", "1"), status = c("padded", "plain"))
labels <- left_join(data.frame(customer_id = c("001", "1")), lookup,
by = c(customer_id = "account"), relationship = "one-to-one")
stopifnot(identical(labels$status, c("padded", "plain")))
left <- data.frame(id = c("A", "A"), year = c(2024L, 2025L))
right <- data.frame(id = c("A", "A"), year = c(2024L, 2025L), value = c(5, 9))
composite <- left_join(left, right, by = c("id", "year"), relationship = "one-to-one")
stopifnot(identical(composite$value, c(5, 9)))
Test duplicate, missing and empty-key cases
A failed relationship assertion is a diagnosis to investigate, not a reason to suppress the guard. Keep legitimate repeated transactions and decide the intended grain. Project only necessary right-side columns when joins would otherwise duplicate large payloads.
fails <- function(expr) inherits(tryCatch(expr, error = identity), "error")
duplicated_customers <- rbind(customers, customers[1, ])
stopifnot(fails(left_join(duplicated_customers, orders, by = "customer_id",
relationship = "one-to-many")))
missing_left <- data.frame(customer_id = NA_character_)
missing_right <- data.frame(customer_id = NA_character_, matched = TRUE)
miss <- left_join(missing_left, missing_right, by = "customer_id", na_matches = "never")
stopifnot(is.na(miss$matched),
nrow(left_join(customers[FALSE, ], orders, by = "customer_id")) == 0,
nrow(left_join(customers, orders[FALSE, ], by = "customer_id")) == 3,
identical(customers$customer_id, c("001", "002", "003")))
