Translate an expression to sql

  con = NULL,
  vars = character(),
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE

  con = NULL,
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE,
  context = list()


..., dots

Expressions to translate. translate_sql() automatically quotes them for you. translate_sql_() expects a list of already quoted objects.


An optional database connection to control the details of the translation. The default, NULL, generates ANSI SQL.


Deprecated. Now call partial_eval() directly.

vars_group, vars_order, vars_frame

Parameters used in the OVER expression of windowed functions.


Use FALSE to suppress generation of the OVER statement used for window functions. This is necessary when generating SQL for a grouped summary.


Use to carry information for special translation cases. For example, MS SQL needs a different conversion for in WHERE vs. SELECT clauses. Expects a list.

Base translation

The base translator, base_sql, provides custom mappings for for commonly used base functions including logical (!, &, |), arithmetic (^), and comparison (!=) operators, as well as common summary (mean(), var()) and manipulation functions.

All other functions will be preserved as is. R's infix functions (e.g. %like%) will be converted to their SQL equivalents (e.g. LIKE). You can use this to access SQL string concatenation: || is mapped to OR, but %||% is mapped to ||. To suppress this behaviour, and force errors immediately when dplyr doesn't know how to translate a function it encounters, using set the dplyr.strict_sql option to TRUE.

You can also use sql() to insert a raw sql string.

SQLite translation

The SQLite variant currently only adds one additional function: a mapping from sd() to the SQL aggregation function STDEV.


# Regular maths is translated in a very straightforward way translate_sql(x + 1)
#> <SQL> `x` + 1.0
translate_sql(sin(x) + tan(y))
#> <SQL> SIN(`x`) + TAN(`y`)
# Note that all variable names are escaped translate_sql(like == "x")
#> <SQL> `like` = 'x'
# In ANSI SQL: "" quotes variable _names_, '' quotes strings # Logical operators are converted to their sql equivalents translate_sql(x < 5 & !(y >= 5))
#> <SQL> `x` < 5.0 AND NOT((`y` >= 5.0))
# xor() doesn't have a direct SQL equivalent translate_sql(xor(x, y))
#> <SQL> `x` OR `y` AND NOT (`x` AND `y`)
# If is translated into case when translate_sql(if (x > 5) "big" else "small")
#> <SQL> CASE WHEN (`x` > 5.0) THEN ('big') WHEN NOT(`x` > 5.0) THEN ('small') END
# Infix functions are passed onto SQL with % removed translate_sql(first %like% "Had%")
#> <SQL> `first` like 'Had%'
translate_sql(first %is% NA)
#> <SQL> `first` is NULL
translate_sql(first %in% c("John", "Roger", "Robert"))
#> <SQL> `first` IN ('John', 'Roger', 'Robert')
# And be careful if you really want integers translate_sql(x == 1)
#> <SQL> `x` = 1.0
translate_sql(x == 1L)
#> <SQL> `x` = 1
# If you have an already quoted object, use translate_sql_: x <- quote(y + 1 / sin(t)) translate_sql_(list(x), con = simulate_dbi())
#> <SQL> `y` + 1.0 / SIN(`t`)
# Windowed translation -------------------------------------------- # Known window functions automatically get OVER() translate_sql(mpg > mean(mpg))
#> Warning: Missing values are always removed in SQL. #> Use `AVG(x, na.rm = TRUE)` to silence this warning #> This warning is displayed only once per session.
#> <SQL> `mpg` > AVG(`mpg`) OVER ()
# Suppress this with window = FALSE translate_sql(mpg > mean(mpg), window = FALSE)
#> Warning: Missing values are always removed in SQL. #> Use `mean(x, na.rm = TRUE)` to silence this warning #> This warning is displayed only once per session.
#> <SQL> `mpg` > AVG(`mpg`)
# vars_group controls partition: translate_sql(mpg > mean(mpg), vars_group = "cyl")
#> <SQL> `mpg` > AVG(`mpg`) OVER (PARTITION BY `cyl`)
# and vars_order controls ordering for those functions that need it translate_sql(cumsum(mpg))
#> Warning: Windowed expression 'SUM(`mpg`)' does not have explicit order. #> Please use arrange() or window_order() to make determinstic.
translate_sql(cumsum(mpg), vars_order = "mpg")