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 &lt;- merge(x = Prior_stg2, y = BankLoans_stg2, 
                            by = "Account_ID", all.x = TRUE)


Any insights?

Example:  

Key1 &lt;- c("A1","A2","A3","A4","A5")  
Key2 &lt;- c("A1","A2","A3","B4","B5")  
BV1 &lt;- c(100, 200, 300, 400, 500)  
BV2 &lt;- c(150, 250, 350, 450, 550)  
df1 &lt;- as.data.frame(cbind(Key1, BV1))  
df2 &lt;- 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
If I understand correctly:

Table1 &lt;- data.frame(key = seq(1,100),a.data = rnorm(100))
Table2 &lt;- 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 &lt;- 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 &lt;- Table1 %&gt;% filter(!key %in% Table2$key)

## Regular R version
R.ans &lt;- Table1[which(!Table1$key %in% Table2$key),]


EDIT after dummy data and expected output

dplyr.ans2 &lt;- 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 &lt;NA&gt;
 5   A5 500 &lt;NA&gt;