Using `data.table` for joins results in unexpected row duplication
I'm currently working with the `data.table` package (version 1.14.2), and I've run into an scenario where performing joins seems to be causing unexpected row duplication. I have two `data.table` objects: `dt1` and `dt2`. The first table, `dt1`, contains unique IDs and some associated values, while the second table, `dt2`, has those same IDs but with multiple entries for some of them. After performing a join, I noticed that the number of rows in the resulting table is much larger than anticipated. Here's the code I've used for the join: ```R library(data.table) dt1 <- data.table(ID = c(1, 2, 3), Value1 = c('A', 'B', 'C')) dt2 <- data.table(ID = c(1, 2, 2, 3), Value2 = c('X', 'Y', 'Z', 'W')) result <- merge(dt1, dt2, by = 'ID', all.x = TRUE) ``` After running this, I expected to see three rows in `result`, but instead, I ended up with four rows: ``` ID Value1 Value2 1: 1 A X 2: 2 B Y 3: 2 B Z 4: 3 C W ``` The duplication of ID 2 is confusing because I thought that when I merged using `by = 'ID'` with `all.x = TRUE`, it would only return matching rows from `dt2` without duplicating them. I've also tried using `unique()` on the resulting table, but that doesn't address the underlying question and might not be suitable for my analysis. Is there a way to handle this situation more effectively? I want to keep the original structure of `dt1` and only append matched values from `dt2` without introducing duplicates unless they actually exist in `dt2` for a reason. Any insights would be appreciated! I've been using R for about a year now. Any feedback is welcome!