These are generic functions that dispatch to individual tbl methods - see the
method documentation for details of individual data sources. x
and
y
should usually be from the same data source, but if copy
is
TRUE
, y
will automatically be copied to the same source as x
.
inner_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"),
...)left_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)
right_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"),
...)
full_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)
semi_join(x, y, by = NULL, copy = FALSE, ...)
anti_join(x, y, by = NULL, copy = FALSE, ...)
tbls to join
a character vector of variables to join by. If NULL
, the
default, *_join()
will do a natural join, using all variables with
common names across the two tables. A message lists the variables so
that you can check they're right (to suppress the message, simply
explicitly list the variables that you want to join).
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
.
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.
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.
other parameters passed onto methods, for instance, na_matches
to control how NA
values are matched. See join.tbl_df for more.
Currently dplyr supports four types of mutating joins and two types of filtering joins.
Mutating joins combine variables from the two data.frames:
inner_join()
return all rows from x
where there are matching
values in y
, and all columns from x
and y
. If there are multiple matches
between x
and y
, all combination of the matches are returned.
left_join()
return all rows from x
, and all columns from x
and y
. Rows in x
with no match in y
will have NA
values in the new
columns. If there are multiple matches between x
and y
, all combinations
of the matches are returned.
right_join()
return all rows from y
, and all columns from x
and y. Rows in y
with no match in x
will have NA
values in the new
columns. If there are multiple matches between x
and y
, all combinations
of the matches are returned.
full_join()
return all rows and all columns from both x
and y
.
Where there are not matching values, returns NA
for the one missing.
Filtering joins keep cases from the left-hand data.frame:
semi_join()
return all rows from x
where there are matching
values in y
, keeping just columns from x
.
A semi join differs from an inner join because an inner join will return
one row of x
for each matching row of y
, where a semi
join will never duplicate rows of x
.
anti_join()
return all rows from x
where there are not
matching values in y
, keeping just columns from x
.
Groups are ignored for the purpose of joining, but the result preserves
the grouping of x
.
# NOT RUN { # "Mutating" joins combine variables from the LHS and RHS band_members %>% inner_join(band_instruments) band_members %>% left_join(band_instruments) band_members %>% right_join(band_instruments) band_members %>% full_join(band_instruments) # "Filtering" joins keep cases from the LHS band_members %>% semi_join(band_instruments) band_members %>% anti_join(band_instruments) # To suppress the message, supply by band_members %>% inner_join(band_instruments, by = "name") # This is good practice in production code # Use a named `by` if the join variables have different names band_members %>% full_join(band_instruments2, by = c("name" = "artist")) # Note that only the key from the LHS is kept # }
Run the code above in your browser using DataCamp Workspace