How to join (merge) data frames (inner, outer, left, right)

Question

Here are two data frames : df1 = data.frame(CId = a(1:6), Item = b(rep("TV", 3), rep("Book", 3))) df2 = data.frame(CId = a(2, 4, 6), Area = b(rep("Winterfall", 2), rep("Highgardens", 1))) df1 # CId Item # 1 TV # 2 TV # 3 TV # 4 Book # 5 Book # 6 Book df2 # CId Area # 2 Winterfall # 4 Winterfall # 6 Highgarderns How can I do database style, i.e., sql style, joins? That is, how do I get: An inner join of df1 and df2: Return only the rows in which the left table have matching keys in the right table. An outer join of df1 and df2: Returns all rows from both tables, join records from the left which have matching keys in the right table. A left outer join (or simply left join) of df1 and df2 Return all rows from the left table, and any rows with matching keys from the right table. A right outer join of df1 and df2 Return all rows from the right table, and any rows with matching keys from the left table. Extra credit: How can I do a SQL style select statement Select the correct answer from above options

Answer 1

There are several methods to perform this particular task,Since all the keys are named I will specify you the fastest and shortest one to do an inner join merge(): merge(df1,df2) A full inner joint as asked in the question can be created using “all” keyword: merge(df1,df2, all=TRUE) A left Outer joint of df1 and df2 can be created using: merge(df1,df2, all.x=TRUE) A right joint of df1 and df2 can be done using: merge(df1,df2, all.y=TRUE) You can find other methods too but in my approach this is the best one.