join

#http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right

df1 <- data.frame(CustomerId = c(1:5), Product = c(rep("Toaster", 3), rep("Radio", 2)))
df2 <- data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

##1
##merge
#Inner join
merge(x = df1, y = df2, by = "CustomerId")
#Full join
merge(x = df1, y = df2, by = "CustomerId", all = TRUE)
#Left join
merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)
#Right join
merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)


##2
#SQL 
library(sqldf)
#Inner join
sqldf("SELECT CustomerId, Product, State
        FROM df1
        JOIN df2 USING(CustomerID)")
#Full join
sqldf("SELECT CustomerId, Product, State 
        FROM df1 LEFT JOIN df2 USING(CustomerID) 
      UNION 
      SELECT CustomerId, Product, State 
        FROM df2 LEFT JOIN df1 USING(CustomerID)")
#Left join
sqldf("SELECT CustomerId, Product, State 
        FROM df1
        LEFT JOIN df2 USING(CustomerID)")

##3
library(dplyr)
#Inner join
inner_join(df1, df2)
#Outer join
full_join(df1, df2)
#Left join
left_join(df1, df2)

##4
library(data.table)
setDT(df1)
setDT(df2)
##Set key
setkey(df1, CustomerId)
setkey(df2, CustomerId)
df1[df2]
df2[df1]
merge(df1,df2,by="CustomerId",all=TRUE)