merge: Merge two dbi.tables
In dbi.table: Database Queries Using 'data.table' Syntax

merge

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

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 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.table`s 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 `NA`s 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.

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.