rquery (version 1.3.3)

natural_join: Make a natural_join node.

Description

Natural join is a join by identity on all common columns specified in the by argument. Any common columns not specified in the by argument are coalesced into a single column preferring the first or "a" table.

Usage

natural_join(a, b, ..., by, jointype = "INNER", env = parent.frame())

Arguments

a

source to select from.

b

source to select from.

...

force later arguments to bind by name

by

character, set of columns to match.

jointype

type of join ('INNER', 'LEFT', 'RIGHT', 'FULL').

env

environment to look to.

Value

natural_join node.

Examples

# NOT RUN {
if(requireNamespace("DBI", quietly = TRUE) &&
   requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(),
                          ":memory:")

  d1 <- rq_copy_to(
    my_db, 'd1',
    build_frame(
      "key", "val", "val1" |
        "a"  , 1  ,  10    |
        "b"  , 2  ,  11    |
        "c"  , 3  ,  12    ))
  d2 <- rq_copy_to(
    my_db, 'd2',
    build_frame(
      "key", "val", "val2" |
        "a"  , 5  ,  13    |
        "b"  , 6  ,  14    |
        "d"  , 7  ,  15    ))

  # key matching join
  optree <- natural_join(d1, d2,
                         jointype = "LEFT", by = 'key')
  execute(my_db, optree) %.>%
    print(.)

  # full cross-product join
  # (usually with jointype = "FULL", but "LEFT" is more
  # compatible with rquery field merg semantics).
  optree2 <- natural_join(d1, d2,
                          jointype = "LEFT", by = NULL)
  execute(my_db, optree2) %.>%
    print(.)
  # notice ALL non-"by" fields take coalese to left table.

  DBI::dbDisconnect(my_db)
}

# }