databases.Rmd

---
layout: default
title: Databases
---

### Two-table verbs

Each two-table verb has a straightforward SQL equivalent:

| 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.