# Left Join with b.key being NULL in R

I am trying to replicate the below sql query in R

select a.*, b.key from Table1 a
LEFT OUTER JOIN Table2 b
on a.key = b.key where b.key is null


I have read through this post however I am still struggling to code my specific case. https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right

I have tried the below but the result doesnot allow me to filter for b.key IS NULL

LoansToInsert_stg1 <- merge(x = Prior_stg2, y = BankLoans_stg2,
by = "Account_ID", all.x = TRUE)


Any insights?

Example:

Key1 <- c("A1","A2","A3","A4","A5")
Key2 <- c("A1","A2","A3","B4","B5")
BV1 <- c(100, 200, 300, 400, 500)
BV2 <- c(150, 250, 350, 450, 550)
df1 <- as.data.frame(cbind(Key1, BV1))
df2 <- as.data.frame(cbind(Key2, BV2))


Expected Output as a new df:

Key1 BV1 Key2 BV2
A1 100 A1 150
A2 200 A2 250
A3 300 A3 350
A4 400 NA NA
A5 500 NA NA

• Could you provide some dummy data and the expected result? Feb 23 '16 at 19:28
– Drj
Feb 23 '16 at 21:39
• see my updated answer Feb 23 '16 at 21:44

If I understand correctly:

Table1 <- data.frame(key = seq(1,100),a.data = rnorm(100))
Table2 <- data.frame(key = c(seq(1,30),rep(NA,30)), b.data = seq(1,60))

##Assuming this is what you want
library(sqldf)
sql.ans <- sqldf("select a.*, b.key from Table1 a LEFT OUTER JOIN Table2 b on a.key = b.key where b.key is null")

## dplyr version
library(dplyr)

dplyr.ans <- Table1 %>% filter(!key %in% Table2$key) ## Regular R version R.ans <- Table1[which(!Table1$key %in% Table2\$key),]


EDIT after dummy data and expected output

dplyr.ans2 <- left_join(df1,df2, by = c("Key1" = "Key2"))
Key1 BV1  BV2
1   A1 100  150
2   A2 200  250
3   A3 300  350
4   A4 400 <NA>
5   A5 500 <NA>

• Yes, however I don't get b.key as part of my resultant. I understand my query has a where clause, however if I want to filter for other keys on Table2, I would not be able to. Basically the df should have key from table 1, key from table 2, other fields. If Key from table 2 (key2) does not exist in Key from table 1, that row could be NA for key2
– Drj
Feb 23 '16 at 21:06
• You don't need to add a WHERE clause, since using a LEFT JOIN instead of an INNER JOIN adds all the rows in df1 that don't have matching keys in df2. Feb 23 '16 at 21:43
• Correct! That would give me the all records in Table1 and matching records in Table2. Your edit brings me closer to what I am expecting. Is there a way to get the key from table2 in the resultant df?
– Drj
Feb 23 '16 at 21:46
• on second thought, I can still work through with this result. Even though this is not ideally what would help me, having BV2 as NA can be used as a proxy for Key2 being NA.
– Drj
Feb 23 '16 at 21:56