merge: Merge two dbi.tables

Description

Merge two dbi.tables. By default, the columns to merge on are determined by the first of the following cases to apply.

If x and y are each unmodified dbi.tables in the same dbi.catalog and if there is a single foreign key relating x and y (either x referencing y, or y referencing x), then it is used to set by.x and by.y.
If x and y have shared key columns, then they are used to set by (that is, by = intersect(key(x), key(y)) when intersect(key(x), key(y)) has length greater than zero).
If x has a key, then it is used to set by (that is, by = key(x) when key(x) has length greater than zero).
If x and y have columns in common, then they are used to set by (that is, by = intersect(names(x), names(y)) when intersect(names(x), names(y)) has length greater than zero).

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 = TRUE,
  suffixes = c(".x", ".y"),
  no.dups = TRUE,
  recursive = FALSE,
  ...
)

Value

a dbi.table.

Arguments

x, y: dbi.tables sharing the same DBI connection. If y is not a dbi.table but does inherit from data.frame, then it is coerced to a dbi.table using as.dbi.table. If y is missing, a merge is performed for each of x's foreign keys.
by: a character 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. When TRUE (default), the key of the merged dbi.table is set to the by / by.x columns.
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 on each dbi.table prior to merging with x. See examples.
...: additional arguments are passed to as.dbi.table.

Details

merge.dbi.table uses sql.join to join x and y then formats the result set to match the typical merge output.

Examples

Run this code

  chinook <- dbi.catalog(chinook.duckdb)

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

  #When y is omitted, x's foreign key relationship is used to determine y
  merge(chinook$main$Album)

  #Track has 3 foreign keys: merge with Album, Genre, and MediaType
  merge(chinook$main$Track)

  #Track references Album but not Artist, Album references Artist
  #This dbi.table includes the artist name
  merge(chinook$main$Track, recursive = TRUE)