Produces a JOIN snippet

JOIN(left_ref, right_tbls, on, cond = NULL, prefer_using = TRUE,
  type = "")

LEFT_JOIN(left_ref, right_tbls, on, cond = NULL, prefer_using = TRUE)

RIGHT_JOIN(left_ref, right_tbls, on, cond = NULL, prefer_using = TRUE)

INNER_JOIN(left_ref, right_tbls, on, cond = NULL, prefer_using = TRUE)

OUTER_JOIN(left_ref, right_tbls, on, cond = NULL, prefer_using = TRUE)

Arguments

left_ref

Reference for left table (name or alias)

right_tbls

Character vector of right-hand side table names, names will be used as table aliases

on

Vector or list of conditions. If a vector, all conditions are applied to all tables. If a list, conditions are matched by order to the RHS table names. Names of entries in any vectors are used as LHS column names.

cond

Optional additional conditions.

prefer_using

Should USING clause be used instead of ON where possible?

type

Join type string (can be lowercase): LEFT, RIGHT, INNER, CROSS, NATURAL LEFT, RIGHT OUTER.

Examples

JOIN('left_tbl', 'right_tbl', 'id')
#> [1] " JOIN right_tbl USING (id)"
LEFT_JOIN('l', c('r' = 'right_tbl'), 'id')
#> [1] "LEFT JOIN right_tbl r USING (id)"
LEFT_JOIN('l', c('r' = 'right_tbl'), 'id', prefer_using = FALSE)
#> [1] "LEFT JOIN right_tbl r ON l.id=r.id"
RIGHT_JOIN('left_tbl', 'right_tbl', c('left.col1' = 'right.col1', 'id2'))
#> [1] "RIGHT JOIN right_tbl ON (left_tbl.`left.col1`=right_tbl.`right.col1` AND left_tbl.id2=right_tbl.id2)"
INNER_JOIN('left_tbl', c('right_1', 'right_2'), 'id_col')
#> [1] "INNER JOIN (right_1, right_2) USING (id_col)"
OUTER_JOIN('l', c(r1 = 'right_1', r2 = 'right_2'), list('col1', 'col2'))
#> [1] "OUTER JOIN (right_1 r1, right_2 r2) ON (l.col1=r1.col1 AND l.col2=r2.col2)"
JOIN(type = "natural right", 'l', c(r1 = 'right_1', r2 = 'right_2'), list(c(left.col1 = 'col1', c(left.col2 = 'col2'))))
#> [1] "NATURAL RIGHT JOIN (right_1 r1, right_2 r2) ON (l.`left.col1`=r1.col1 AND l.`left.col2`=r1.col2 AND l.`left.col1`=r2.col1 AND l.`left.col2`=r2.col2)"