translation-verb.Rmd

---
title: "Verb translation"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Verb translation}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r}
#| include: false
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
options(tibble.print_min = 6L, tibble.print_max = 6L, digits = 3)
```

There are two parts to dbplyr SQL translation: translating dplyr verbs, and translating expressions within those verbs. This vignette describes how entire verbs are translated; `vignette("translation-function")` describes how individual expressions within those verbs are translated.

All dplyr verbs generate a `SELECT` statement. To demonstrate we'll make a temporary database with a couple of tables

```{r, message = FALSE}
library(dbplyr)
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
flights <- copy_to(con, nycflights13::flights)
airports <- copy_to(con, nycflights13::airports)
```

## Single table verbs

*  `select()` and `mutate()` modify the `SELECT` clause:

    ```{r}
    flights |>
      select(contains("delay")) |>
      show_query()
    
    flights |>
      select(distance, air_time) |>  
      mutate(speed = distance / (air_time / 60)) |>
      show_query()
    ```
    
*  `filter()` generates a `WHERE` clause:
    
    ```{r}
    flights |> 
      filter(month == 1, day == 1) |>
      show_query()
    ```

*  `arrange()` generates an `ORDER BY` clause:

    ```{r}
    flights |> 
      arrange(carrier, desc(arr_delay)) |>
      show_query()
    ```

*  `summarise()` and `group_by()` work together to generate a `GROUP BY` clause:

    ```{r}
    flights |>
      group_by(month, day) |>
      summarise(delay = mean(dep_delay, na.rm = TRUE)) |>
      show_query()
    ```

## Subqueries

It's not always possible to translate a single dplyr verb into a single SQL query. For example, in SQL, variables in the `SELECT` clause have to come from another table; you can't refer to a variable that you just created. For that reason, dbplyr will create subqueries where needed:

```{r}
flights |>
  select(distance, air_time) |>  
  mutate(
    air_time_h = air_time / 60,
    speed = distance / air_time_h) |>
  show_query()
```

It's also possible to use a CTE if you so desire:

```{r}
flights |>
  select(distance, air_time) |>  
  mutate(
    air_time_h = air_time / 60,
    speed = distance / air_time_h) |>
  show_query(sql_options = sql_options(cte = TRUE))
```

Sometimes dbplyr will create a subquery where it's not strictly necessary. We strive to avoid this as much as possible, but our analysis of the generated SQL is not always complete, so we'll typically err on the side of safety (creating more subqueries) rather than performance.

It's important to know that most SQL dialects either error or include `LIMIT` and `ORDER BY` statements inside of subqueries. For that reason, you should always put `head()` and `arrange()` as late as possible in your pipeline.

## Dual table verbs

| R                | SQL
|------------------|------------------------------------------------------------
| `inner_join()`   | `SELECT * FROM x JOIN y ON x.a = y.a`
| `left_join()`    | `SELECT * FROM x LEFT JOIN y ON x.a = y.a`
| `right_join()`   | `SELECT * FROM x RIGHT JOIN y ON x.a = y.a`
| `full_join()`    | `SELECT * FROM x FULL JOIN y ON x.a = y.a`
| `semi_join()`    | `SELECT * FROM x WHERE EXISTS (SELECT 1 FROM y WHERE x.a = y.a)`
| `anti_join()`    | `SELECT * FROM x WHERE NOT EXISTS (SELECT 1 FROM y WHERE x.a = y.a)`
| `intersect(x, y)`| `SELECT * FROM x INTERSECT SELECT * FROM y`
| `union(x, y)`    | `SELECT * FROM x UNION SELECT * FROM y`
| `setdiff(x, y)`  | `SELECT * FROM x EXCEPT SELECT * FROM y`

`x` and `y` don't have to be tables in the same database. If you specify `copy = TRUE`, dplyr will copy the `y` table into the same location as the `x` variable. This is useful if you've downloaded a summarised dataset and determined a subset of interest that you now want the full data for. You can use `semi_join(x, y, copy = TRUE)` to upload the indices of interest to a temporary table in the same database as `x`, and then perform a efficient semi join in the database. 

If you're working with large data, it maybe also be helpful to set `auto_index = TRUE`. That will automatically add an index on the join variables to the temporary table.