How do I run a SQL left join in R?

Question

Relatively new to R and I haven't been able to find online what would be the equivalent of a SQL left join. Let's say I have data that looks like:

School| Year| Grade              | #students | math_approached| math_metorexceeded
610534| 2016| Mathematics Grade 3| 57        | 5.3%           | 94.7%
610534| 2016| Mathematics Grade 4| 60        | 8.3%           | 91.7%
610534| 2016| Mathematics Grade 5| 59        | 6.8%           | 93.2%
610534| 2015| Mathematics Grade 3| 57        | 5.3%           | 94.7%
610534| 2015| Mathematics Grade 4| 60        | 8.3%           | 91.7%
610534| 2015| Mathematics Grade 5| 59        | 6.8%           | 93.2%
699999| 2015| Mathematics Grade 3| 51        | 5.3%           | 94.7%
699999| 2015| Mathematics Grade 4| 61        | 8.3%           | 91.7%
699999| 2015| Mathematics Grade 5| 53        | 6.8%           | 93.2%

I'm trying to find the Math % approached value for the school grade's prior year. In SQL, this would look like

select a.*, b.math_approached, b.math_metorexceeded
from mydata as a
left join mydata as b
  on a.school = b.school
  and a.grade = b.grade
  and b.year = '2015'
  and a.year = '2016'

And back in R, I have a dataframe df holding all the data. It has

df$school
df$year
df$grade
df$students
df$math..approached
df$math..met.or.exceeded

as its columns

Answer 1

One option available to you, which would require a minimal amount of extra work, would be to use the sqldf package, which lets you run actual SQL queries on your data frames within R. The code is straightforward:

library(sqldf)

query <- "select a.*, b.math_approached, b.math_metorexceeded
    from df as a
    left join df as b
        on a.school = b.school
        and a.grade = b.grade
        and b.year = '2015'
        and a.year = '2016'"

result <- sqldf(query)

The only change I had to make to your raw SQL query was to replace the SQL table name mydata with the name of the data frame in R containing the same information, df.