[R] THE EQUIVALENT OF SQL INNER TABLE JOIN IN R

[Wacek Kusnierczyk]

Peter Dalgaard wrote:
> Nigel Birney wrote:
>> Hello all,
>>
>> Apologize for the newbie question. What's the easiest way to do a SQL
>> inner
>> table join in R?
>> Say I have a table containing column names A, B, C and another which has
>> columns named C, D, E. I would like to do an inner table join on C and
>> produce a table A, B, C, D, E.
>
> merge(), perhaps? Otherwise describe what an inner table join does.

btw., i think ?merge has it wrong when it comes to the sql join terminology:

" In SQL database terminology, the default value of 'all = FALSE'
     gives a _natural join_, a special case of an _inner join_."

following [1, sec. 6.5] (and in concordance with the typical use of the
terms in the db lingo, as of my rather limited knowledge), a natural
join is a join where values are compared pairwise for columns with the
same names across the joined tables.  the result from merge with
all=FALSE does not have to be a natural join, while it will be an inner
join, as in:

    d1 = data.frame(a=1:5, b=rnorm(5))
    d2 = data.frame(c=3:7, d=rnorm(5))

    merge(d1, d2, all=FALSE)
    # 25 rows, a cross join (an outer join)
     # *not* an inner join, even less so a natural join

    merge(d1, d2, by.x='a', by.y='c', all=FALSE)
    # 3 rows, an inner join
    # *not* a natural join

the point is, all=FALSE gives a natural join iff by is equivalent to
intersect(names(x), names(y)), and these two conditions together are
necessary (and sufficient) for a join to be a natural join.

the snippet from ?merge quoted above is wrong and misleading, and should
be corrected to sth like:

" In SQL database terminology, the default value of 'all = FALSE'
     gives an _inner join_.  If, in addition, 'by' is equivalent to
'intersect(names(x), names(y))', the the join     is a _natural join_, a
special case of an _inner join_."

or, if the authors insist ?merge is correct, would they provide a reference?

(in fact, the terminology is not that coherent;  e.g., in mysql natural
merely refers to column names, and not to how to choose rows, and one
can have natural outer joins -- which are not, in general, inner joins.)


vQ

[1] c.j. date's, sql and relational theory, o'reilly 2009