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

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.