merge: Merge two dbi.tables

mergeR Documentation

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.

  1. 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.

  2. 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).

  3. 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).

  4. 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 class '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,
  ...
)

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.

Value

a dbi.table.

See Also

merge.data.table, merge.data.frame

Examples

  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)


dbi.table documentation built on June 29, 2025, 1:07 a.m.