SQL translation:

  • sql_expr_matches(con, x, y) generates an alternative to x = y when a pair of NULLs should match. The default translation uses a CASE WHEN as described in https://modern-sql.com/feature/is-distinct-from.

  • sql_translation(con) generates a SQL translation environment.


  • sql_table_analyze(con, table) generates SQL that "analyzes" the table, ensuring that the database has up-to-date statistics for use in the query planner. It called from copy_to() when analyze = TRUE.

  • sql_table_index() generates SQL for adding an index to table. The

Query manipulation:

  • sql_query_explain(con, sql) generates SQL that "explains" a query, i.e. generates a query plan describing what indexes etc that the database will use.

  • sql_query_fields() generates SQL for a 0-row result that is used to capture field names in tbl_sql()

  • sql_query_save(con, sql) generates SQL for saving a query into a (temporary) table.

  • sql_query_wrap(con, from) generates SQL for wrapping a query into a subquery.

Query generation:

  • sql_query_select() generate SQL for a SELECT query

  • sql_query_join() generate SQL for joins

  • sql_query_semi_join() generate SQL for semi- and anti-joins

  • sql_query_set_op() generate SQL for UNION, INTERSECT, and EXCEPT queries.

sql_expr_matches(con, x, y)


sql_table_analyze(con, table, ...)

sql_table_index(con, table, columns, name = NULL, unique = FALSE, ...)

sql_query_explain(con, sql, ...)

sql_query_fields(con, sql, ...)

sql_query_save(con, sql, name, temporary = TRUE, ...)

sql_query_wrap(con, from, name = unique_subquery_name(), ...)

sql_query_rows(con, sql, ...)

  where = NULL,
  group_by = NULL,
  having = NULL,
  order_by = NULL,
  limit = NULL,
  distinct = FALSE,
  subquery = FALSE

  type = "inner",
  by = NULL,
  na_matches = FALSE,

sql_query_semi_join(con, x, y, anti = FALSE, by = NULL, ...)

sql_query_set_op(con, x, y, method, ..., all = FALSE)

dbplyr 2.0.0

Many dplyr::db_* generics have been replaced by dbplyr::sql_* generics. To update your backend, you'll need to extract the SQL generation out of your existing code, and place it in a new method for a dbplyr sql_ generic.

The query generating functions have also changed names. Their behaviour is unchanged, so you just need to rename the generic and import from dbplyr instead of dplyr.

Learn more in vignette("backend-2.0")

See also