dbi.table (version 1.0.1)

merge: Merge two dbi.tables

Description

Merge two dbi.tables. The dbi.table method is similar to the data.table method except that the result set is only determined up to row order and is not sorted by default.

Default merge columns: if x has a foreign key constraint that references y then the columns comprising this key are used; see details. When a foreign key cannot be found, then the common columns between the two dbi.tabless are used.

Use the by, by.x, and by.y arguments explicitly to override this default.

Usage

# S3 method for dbi.table
merge(
  x,
  y,
  by = NULL,
  by.x = NULL,
  by.y = NULL,
  all = FALSE,
  all.x = all,
  all.y = all,
  sort = FALSE,
  suffixes = c(".x", ".y"),
  no.dups = TRUE,
  recursive = FALSE,
  ...
)

Value

a dbi.table.

Arguments

x, y

dbi.tables sharing the same DBI connection.

by

A vector of shared column names in x and y to merge on.

by.x, by.y

character vectors of column names in x and y to merge on.

all

a logical value. all = TRUE is shorthand to save setting both all.x = TRUE and all.y = TRUE.

all.x

a logical value. When TRUE, rows from x that do not have a matching row in y are included. These rows will have NAs in the columns that are filled with values from y. The default is FALSE so that only rows with data from both x and y are included in the output.

all.y

a logical value. Analogous to all.x above.

sort

a logical value. Currently ignored.

suffixes

a length-2 character vector. The suffixes to be used for making non-by column names unique. The suffix behavior works in a similar fashion to the merge.data.frame method.

no.dups

a logical value. When TRUE, suffixes are also appended to non-by.y column names in y when they have the same column name as any by.x.

recursive

a logical value. Only used when y is missing. When TRUE, merge is called recursively on each of the just-merged dbi.tables. See examples.

...

additional arguments are ignored.

Details

Foreign key constraints. Foreign keys can only be queried when (1) the dbi.table's schema is loaded, and (2) dbi.table understands the underlying database's information schema. The merge is done at the SQL level and can merge on columns that are not in the dbi.tables.

This function uses sql.join to join x and y then formats the result set to match the typical merge result.

Examples

  dbi.attach(chinook.duckdb, name = "merge-example")

  #The Album table has a foreign key constriant that references Artist
  merge(Album, Artist)

  #When y is omitted, x's foreign key realtionship is used to determin y
  merge(Album)

  #Multiple foreign keys are supported
  csql(merge(Track))

  #Track reference Album but not Artist, Album does reference Artist
  #This dbi.table includes Artist.Name as well
  csql(merge(Track, recursive = TRUE))

  detach("duckdb:merge-example")