These are methods for the dplyr generics left_join(), right_join(), inner_join(), full_join(), anti_join(), and semi_join(). The mutating joins (left, right, inner, and full) are translated to data.table::merge.data.table(), except for the special cases where it's possible to translate to [.data.table. Semi- and anti-joins have no direct data.table equivalent.

# S3 method for dtplyr_step
left_join(x, y, ..., by = NULL, copy = FALSE, suffix = c(".x", ".y"))

Arguments

x, y

A pair of lazy_dt()s.

...

Other parameters passed onto methods.

by

A character vector of variables to join by.

If NULL, the default, *_join() will perform a natural join, using all variables in common across x and y. A message lists the variables so that you can check they're correct; suppress the message by supplying by explicitly.

To join by different variables on x and y, use a named vector. For example, by = c("a" = "b") will match x$a to y$b.

To join by multiple variables, use a vector with length > 1. For example, by = c("a", "b") will match x$a to y$a and x$b to y$b. Use a named vector to match different variables in x and y. For example, by = c("a" = "b", "c" = "d") will match x$a to y$b and x$c to y$d.

To perform a cross-join, generating all combinations of x and y, use by = character().

copy

If x and y are not from the same data source, and copy is TRUE, then y will be copied into the same src as x. This allows you to join tables across srcs, but it is a potentially expensive operation so you must opt into it.

suffix

If there are non-joined duplicate variables in x and y, these suffixes will be added to the output to disambiguate them. Should be a character vector of length 2.

Examples

library(dplyr, warn.conflicts = FALSE) band_dt <- lazy_dt(dplyr::band_members) instrument_dt <- lazy_dt(dplyr::band_instruments) band_dt %>% left_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [3 x 3] #> Call: `_DT20`[`_DT19`, on = .(name), allow.cartesian = TRUE] #> #> name plays band #> <chr> <chr> <chr> #> 1 Mick NA Stones #> 2 John guitar Beatles #> 3 Paul bass Beatles #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results
band_dt %>% right_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [3 x 3] #> Call: `_DT19`[`_DT20`, on = .(name), allow.cartesian = TRUE] #> #> name band plays #> <chr> <chr> <chr> #> 1 John Beatles guitar #> 2 Paul Beatles bass #> 3 Keith NA guitar #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results
band_dt %>% inner_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [2 x 3] #> Call: merge(`_DT19`, `_DT20`, all = FALSE, by.x = "name", by.y = "name", #> allow.cartesian = TRUE) #> #> name band plays #> <chr> <chr> <chr> #> 1 John Beatles guitar #> 2 Paul Beatles bass #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results
band_dt %>% full_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [4 x 3] #> Call: merge(`_DT19`, `_DT20`, all = TRUE, by.x = "name", by.y = "name", #> allow.cartesian = TRUE) #> #> name band plays #> <chr> <chr> <chr> #> 1 John Beatles guitar #> 2 Keith NA guitar #> 3 Mick Stones NA #> 4 Paul Beatles bass #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results
band_dt %>% semi_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [2 x 2] #> Call: `_DT19`[unique(`_DT19`[`_DT20`, which = TRUE, nomatch = NULL, #> on = .(name)])] #> #> name band #> <chr> <chr> #> 1 John Beatles #> 2 Paul Beatles #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results
band_dt %>% anti_join(instrument_dt)
#> Joining, by = "name"
#> Source: local data table [1 x 2] #> Call: `_DT19`[!`_DT20`, on = .(name)] #> #> name band #> <chr> <chr> #> 1 Mick Stones #> #> # Use as.data.table()/as.data.frame()/as_tibble() to access results