Cross join in R with filtering/matching

Question

I want to do a cross join in R. If we have no conditions, then the data.table package will do it. But what would be the equivalent of this SQL query in R?

Select table1.value as value1, table2.value as value2
from table1 cross join table2 where table1.id = table2.id

consider this example

table1:
id  value
1   1
1   2
2   1
3   1
3   3
3   4

table2:
id  value
1   5
1   4
2   1
3   4
3   3

in this case my desired output is:

value1 value2
1      5
1      4
2      5
2      4
1      1
1      4
1      3
3      4
3      3
4      4
4      3

please note that neither the id column is unique nor the result set has unique rows.

Answer 1

Columns value.x and value.y are what you need

   table1 <- data.frame(id= c(1,1,2,3,3,3), value=c(1,2,1,1,3,4))
   table2 <- data.frame(id=c(1,1,2,3,3), value=c(5,4,1,4,3))
   merge(table1,table2,by="id",all.y=TRUE)



   id    value.x  value.y
1   1       1       5
2   1       1       4
3   1       2       5
4   1       2       4
5   2       1       1
6   3       1       4
7   3       1       3
8   3       3       4
9   3       3       3
10  3       4       4
11  3       4       3